Using the where clause in Editor to limit records

kkuti

I have tried but failed to get the where clause to Limit the number of records selected. I have used other multiple where conditions as they have worked but perhaps it is a syntax issue to get the Limit clause to work. Any suggestions please

kthorngren

where clause to Limit the number of records selected

How are you selecting the records?

perhaps it is a syntax issue to get the Limit clause to work.

Maybe. Can you post the code you are using to create this?

@allan will probably be the one to help you but please post more details of what you are doing and the code you have tried.

Kevin

kkuti

Thanks, the first where clause is
->where('created_by', $_SESSION['userlog'])
How do I use the limit clause to get just the last 30 records based on the created_date field please

allan

There isn't an option in the Editor PHP libraries to manually set the LIMIT clause in the SQL.

To be clear, the LIMIT has nothing to do with the WHERE clause. Obviously when you set a WHERE condition, you are likely to have less records returned, but a LIMIT will truncate the response.

I'm not clear on why you would want to set a LIMIT? If you inverted the ordering you'd have records appear that weren't there before, and records that were there disappearing. Indeed, if you had more than 60 records, you'd have some that you never see.

Can you explain the use case for me please?

Allan

kkuti

Good evening Allan, for each candidate they have about say 60 answers, we need to find the most recent 30 answers to grade their exams. Thus we want to run the query then 'order by descending' and then limit it to 30 which will give us the last 30 records. We can already sum up their grades on all their answers but we need their most recent 30 answers hence why we want to use the Limit clause. I have tried using subqueries but MySQL and Mariadb do not support using Limit in a subquery such as WHERE IN. Perhaps I need to think more I guess. We add up their scores using the SUM() aggregate function and I tried to limit the rows for the SUM() to be added up hence the subquery, however Limit is not supported in MySQL and Mariadb subqueries. Hope I have explained it properly. Thanks

allan

What you could do is:

->where(function ($q) {
  $q->limit(30);
});

Its a little sneaky, but using where() with a function gives you access to the underlying Query so you can call methods like that.

You'd need to make sure that the sorting is sensible - i.e sort by the latest update time or something, otherwise I think it will get really confusing.

Allan

kkuti

Allan good evening, that has worked and I am able to add the limit to the query. I will now add other constraints for sorting et al. Thank you very much

allan

Are you just fetching the data from the server-side, or are you actually doing editing on this table? If it is readonly, then you might be well served to use the $db->query() method and build the query you want:

$data = $db
    ->query('select')
    ->table(...)
    ->get(...)
    ->where(...)
    ->order(...)
    ->limit(30)
    ->exec()
    ->fetchAll();

Of course that won't work with server-side processing, but as a simple case for performing a select, it might be useful.

Allan