SOQL in a snippet

edited June 29, 2020 in Questions
Hey! Is it possible to use a snippet to make a soql query and return results in a similar way to the process for doing a sosl search (skuid.sfdc.search). I need to do this in a snippet as opposed to just using a Skuid model because I need to access the OFFSET condition in the SOQL request. Any help much much much appreciated. Cheers! Louis

Comments

  • edited September 3, 2019
    If I were trying this, I would use:

    var myModel = skuid.model.getModel("MyModel");
    myModel.soql = "SELECT...";
    myModel.updateData(callback function);

    The documentation (https://docs.skuid.com/v11.0.1/en/skuid/api/skuid_model_model.html) does not indicate whether 'soql' is readonly or not. 

    Perhaps a workaround using myModel.orderByClause to tack clauses onto the soql string would work if the soql field is read only.


  • edited July 31, 2018
    Hi Mike!

    A fantastic idea, but I wasn't able to write to the soql property, or at least updateData didn't honour the changes.

    I'm trying to build some custom pagination that will query only the records defined in the range (e.g. "Showing Accounts 20-29") each time you select 'next/prev/somePageNumber'. I thought maybe 'loadNextOffsetPage' might help me out but it doesn't quite cut it.

  • edited September 3, 2019
    I'm hearing that as a mixing of back end query and front end display. Pagination happens after rows have been retrieved, so I'm not clear on why you want to change the query/soql after the fact. And doesn't the standard skuid table footer show the start and end row numbers automatically?

    Also, " The merge syntax {{index}} gives the row number from the model. ", but I'm not sure if that will help.

    Are you dealing with an Apex Heap Size error that requires you to limit your data?

    Do you want your snippet to update model conditions rather than the (resultant) soql?

  • edited July 31, 2018
    Thanks Mike, it's late here and I'm starting to become delirious but:

    We have thousands of records that we need to list on a public site and we need users to be able to cycle through ALL of them. Removing the model limit on the page breaks the page (yeah - Apex heap issues or severe performance degradation).

    Skuid's pagination uses a "Load More" button which a user can use to pull in more data which is not satisfactory for our purposes.

    So - usually how you'd build this in a website/database is that your pagination would drive the query:

    So when you're on page 1 showing records 1-10 your query has a limit of 10 and an offset of 0. When you're on page 2 it triggers a new query with a limit of 10 but an offset of 10 which then returns records 11-20, and so on. So each time you click 'Next' in the pagination or click on a page number you're only ever then querying the required records. This is why I'm trying to get hold of the offset property.

    I can do this for SOSL but SOSL requires a search term. The page I'm trying to build needs to also list all the records when no search term is entered. Hence the SOQL route...

    It's a tough one!
  • Pat VachonPat Vachon 💎💎💎
    edited June 25, 2020
    How many records? Is it acceptable to have a rather long load time in order to load them all?
  • edited July 31, 2018
    ~5000 - the load time would be a problem if it's excessive/noticeable - not sure how to quantify that!
  • edited July 31, 2018
    I know there isn't much practical likelihood that someone will cycle through all 5000 records but (for *reasons*) the nature of the project requires that each record in the list is equally available (as opposed to just showing the first 500, for example).
  • edited July 31, 2018
    Oooooh... model.loadAllRemainingRecords() - I'd taken a look at that but didn't quite see the application (probably because of the aforementioned delirium). Let me take a look... 
  • edited July 31, 2018
    Cooooool - yes, I can use loadAllRemainingRecords to load up all the data without getting heap errors and the like - now just need to see if I can work it into the rest of the stuff going on in the page.

    I'd still love to know if there's a way to set the range being queried (e.g. 21 to 30) but I'll try this approach for now - thanks so much!
  • edited July 31, 2018
    So close! It loads all the records and you can page through them using my custom navigation but the rest of the window becomes unresponsive (it's a Salesforce Community). Back to the drawing board.
  • Pat VachonPat Vachon 💎💎💎
    edited July 31, 2018
    loadAllRemainingRecords doesn't work past offset of 2000.
  • Matt SonesMatt Sones 💎💎💎
    edited August 1, 2018
    Maybe I'm missing something, but can't you just have your 'next page' button call loadNextOffsetPage()?
  • edited August 1, 2018
    Hey Matt,

    This is a good shout - I'm actually working on a prototype for this but I think this approach restricts the navigation to Prev/Next buttons whereas the design I'm trying to reach is like this:

    First - Prev - 1 - 2 - 3 - 4 - 5 (etc.) - Next - Last

  • Matt SonesMatt Sones 💎💎💎
    edited August 1, 2018
    You would definitely be limited there.

    You may have to just start with First - Prev - 1 - Next

    But then as user click next a few times you should be able to get to something like

    First - Prev - 1 - 2 - 3 - 4 - 5 (etc.)- Next

    You just wouldn't be able to use "Last".

    You would only want your custom Next to be active if the user was on the last available set of records.



    To do that, I would just leverage the existing skuid table pagination, but hide the default Next and Last via jQuery to begin with, and add your custom 'Next' link. Then every time pagination happens (perhaps there's a table event you could tap into?), you would need to run some javascript/jQuery to determine if you're on the last available set of records, and show the standard Next or your custom Next.
  • edited August 1, 2018
    Thanks Matt - I'm using a template component to render the list of results and have found a javascript pagination library that I can use to navigate through them. I hadn't thought of creating the pages dynamically as you start clicking 'Next' - that's an interesting idea but it might be counter-intuitive. I think if I can't query a range of records I'll have to stick with only using next/prev pagination and just hope that we're allowed to take a hit on not meeting the designs! The frustrating thing is that if I was writing this page in a Lightning component using a SOQL query in an apex class I can get this to work. I can use skuid.sfdc.sosl to get this functionality to work when there is a search term (as I can supply an offset property) but I can't get Skuid to do this with a SOQL query - and my page needs to cover both eventualities! Perhaps the answer is to make this page without Skuid - but that's no fun!
  • edited August 1, 2018
    I was wondering if it was possible to use skuid.sfdc.api() to do a SOQL query but I can't find anything on that...
  • edited June 29, 2020
    Hi Louis,

    There is also the skuid.sfdc.query function, where you can include a SOQL query like you would do in the Salesforce Developer Console. See below an example using the OFFSET Property:

    var queryString = "SELECT Id, Name, Industry, Phone, ShippingCountry From Account WHERE ShippingCountry = 'UK' ORDER BY Name Limit 10 OFFSET 2";  skuid.$.when(skuid.sfdc.api.query(queryString))  .done(function(queryResult) {   var records = queryResult.records;   console.log(records);  })  .fail(function(queryResult) {   console.error('Search failed: ' + queryResult.error);  }).always(function(queryResult) {   console.log('Original search request');   console.log(queryString);  }); 

    Cheers,
    Osman
  • edited August 7, 2018
    Hi Osman, this is fab - just what I was looking for - thank you! However, I can get this to work when I preview a Skuid page in Salesforce Classic but when I run the same page in a Salesforce Lightning Community it does not run - any ideas?

    Cheers!

    Louis
  • edited February 14, 2020
    Here's the super simple test page I used:

    <skuidpage unsavedchangeswarning="yes" personalizationmode="server" showsidebar="true" useviewportmeta="true" showheader="true">
        <models/>
        <components>
            <buttonset uniqueid="sk-KX6-275">
                <buttons>
                    <button type="custom" label="Do SOQL Query" uniqueid="sk-KX6-284" snippet="doSOQL"/>
                </buttons>
            </buttonset>
        </components>
        <resources>
            <labels/>
            <javascript>
                <jsitem location="inlinesnippet" cachelocation="false" name="doSOQL">var queryString = "SELECT Id, Name From Account LIMIT 10";
    console.log("QUERY");
    skuid.$.when(skuid.sfdc.api.query(queryString))
    .done(function(queryResult) {
    var records = queryResult.records;
    console.log(records);
    })
    .fail(function(queryResult) {
    console.error('Search failed: ' + queryResult.error);
    }).always(function(queryResult) {
    console.log('Original search request');
    console.log(queryString);
    });</jsitem>
            </javascript>
            <css/>
            <actionsequences/>
        </resources>
        <styles>
            <styleitem type="background" bgtype="none"/>
        </styles>
    </skuidpage>
  • edited August 7, 2018
    Hi Louis,

    Yes, to make it work in Salesforce Lightning Community, we have to do some adjustments:

    1) Create a VisualforcePage and make sure the "Available for Lightning Experience, Lightning Communities, and the mobile app" - checkbox is checked and connect it to your Skuid Page with the right markup.

    The markup looks something like this:
    <apex:page   readonly="true"   showheader="false"   sidebar="false"   doctype="html-5.0"   title="soqlTest">     <skuid:page page="soqlTest"/> </apex:page>
    Just change the skuid page Name or let the markup be generated as usual here:
    https://docs.skuid.com/v10.0.4/en/skuid/deploy/salesforce/visualforce/skuid-page-visualforce-compone...

    2) In the Skuid Page Click on More Page Actions -> Generate Page Support File

    3) Include the generated Visualforce Page into your Community page using the Visualforce Page Component.

    This should solve the issue. (I'm not sure if the Preview in the Community Page Builder is working correctly as it's broken there for me, but after 'publishing' the community page, the JS works).

    Cheers,
    Osman
  • edited August 7, 2018
    Hi Osman,

    Thanks for that. Unfortunately I can't use a visualforce page as this Skuid page is embedded in a custom Lightning Component and there is a lot of event communication between the skuid page and the lightning component. We also don't want to use visualforce pages on this project.

    I've solved my immediate issue with loading all the data on the page by building our own lightweight pagination solution. All the data (~5000 records) is pulled into the page and then the pagination just displays a page of those records at a time with previous/next controls (previously the pagination would first render all records and then following this would generate the pagination and re-render which was proving expensive in the browser). Out custom pagination seems to be working okay but it would be better if we could just dynamically query a range of records via SOQL. Are their plans to incorporate skuid.sfdc.api.query into Lightning? We might then be able to build a solution around it. For now we'll stick with our current approach. Cheers!
  • Bill McCulloughBill McCullough 💎💎
    edited August 9, 2018
    Louis,

    Would Salesforce's Ajax Toolkit help?  You can pass in a dynamic SOQL and get an array of records back.

    https://developer.salesforce.com/docs/atlas.en-us.ajax.meta/ajax/sforce_api_ajax_more_samples.htm

    You'll need to load the data into your model, but you can do query more.

    Thanks,

    Bill
  • edited August 9, 2018
    Hi Bill, that’s a fab idea - I’ve done some work with Salesforce’s rest api via jsforce but not from within a Skuid page. I wonder what the security implications would be running the Ajax login from a Skuid snippet as the site is on a public community... Thank you for your input! For now I’m just loading housings of records on the page and have built some lightweight pagination to cycle through the data. I’m hoping Skuid will release soql api queries from Lightning at a future date so I can tidy this up a little. Cheers!
  • Mark LMark L ✭✭✭✭
    edited March 25, 2020
    skuid.sfdc.api.query() is a very powerful function to now be aware of. This will assist incredibly.

    Thank you!!!
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!