SOQL in a snippet

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.

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.

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?

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!

How many records? Is it acceptable to have a rather long load time in order to load them all?

~5000 - the load time would be a problem if it’s excessive/noticeable - not sure how to quantify that!

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).

https://community.skuid.com/t/automatic-load-more-action may help too.

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… 

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!

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.

loadAllRemainingRecords doesn’t work past offset of 2000.

Maybe I’m missing something, but can’t you just have your ‘next page’ button call loadNextOffsetPage()?

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

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.

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!

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…

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); }); 

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

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>

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>

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!

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

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!

skuid.sfdc.api.query() is a very powerful function to now be aware of. This will assist incredibly.

Thank you!!!