SOQL in a snippet

  • 1
  • Question
  • Updated 3 months ago
  • Answered
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
Photo of Louis Skelton

Louis Skelton

  • 4,878 Points 4k badge 2x thumb

Posted 4 months ago

  • 1
Photo of Mike Dwyer

Mike Dwyer

  • 3,390 Points 3k badge 2x thumb
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.


Photo of Louis Skelton

Louis Skelton

  • 4,878 Points 4k badge 2x thumb
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.
Photo of Mike Dwyer

Mike Dwyer

  • 3,390 Points 3k badge 2x thumb
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?

Photo of Louis Skelton

Louis Skelton

  • 4,878 Points 4k badge 2x thumb
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!
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
How many records? Is it acceptable to have a rather long load time in order to load them all?
Photo of Louis Skelton

Louis Skelton

  • 4,786 Points 4k badge 2x thumb
~5000 - the load time would be a problem if it's excessive/noticeable - not sure how to quantify that!
Photo of Louis Skelton

Louis Skelton

  • 4,786 Points 4k badge 2x thumb
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).
Photo of Mike Dwyer

Mike Dwyer

  • 3,390 Points 3k badge 2x thumb
Photo of Louis Skelton

Louis Skelton

  • 4,878 Points 4k badge 2x thumb
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... 
Photo of Louis Skelton

Louis Skelton

  • 4,878 Points 4k badge 2x thumb
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!
Photo of Louis Skelton

Louis Skelton

  • 4,878 Points 4k badge 2x thumb
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.
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
loadAllRemainingRecords doesn't work past offset of 2000.
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Maybe I'm missing something, but can't you just have your 'next page' button call loadNextOffsetPage()?
Photo of Louis Skelton

Louis Skelton

  • 4,786 Points 4k badge 2x thumb
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
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
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.
Photo of Louis Skelton

Louis Skelton

  • 4,786 Points 4k badge 2x thumb
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!
Photo of Louis Skelton

Louis Skelton

  • 4,786 Points 4k badge 2x thumb
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...
Photo of Osman Jakupi

Osman Jakupi

  • 110 Points 100 badge 2x thumb
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)
Photo of Louis Skelton

Louis Skelton

  • 4,786 Points 4k badge 2x thumb
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>
Photo of Osman Jakupi

Osman Jakupi

  • 110 Points 100 badge 2x thumb
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)
Photo of Louis Skelton

Louis Skelton

  • 4,786 Points 4k badge 2x thumb
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!
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,436 Points 10k badge 2x thumb
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
Photo of Louis Skelton

Louis Skelton

  • 4,786 Points 4k badge 2x thumb
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!