LIMIT on prefilters

[tutux]

HI

i would like to display only the penultimate of a list
in sql, the code works perfectly for me :

SELECT * FROM `articles` ORDER BY id DESC LIMIT 1,1;

but how to place it in prefilters?

Regards

 

[tutux]

friendly bump

 

[troester]

Try prefilter
WHERE id IN
value: SELECT id FROM `articles` ORDER BY id DESC LIMIT 1,1;
Type: query

 

[tutux]

thank you !

mmm... it doesn't work, i tried a lot of possibilities but the error message is :

getData:You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''1;') ) LIMIT 0, 1' at line 35 SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT......

Here is my screenshot of SQL fields

 

[troester]

I only did copy/paste of your query. Try
...limit 1

gesendet mit Tapatalk

 

[troester]

ok, on my site I get
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery
so this seems not to be possible.

This is working on my site: put
SELECT id FROM `articles` ORDER BY id DESC LIMIT 0,1
in the "Pre-filter query" field and then
WHERE id EQUALS
value: {$q->id}
type: text

 

[tutux]

Oh yes it works !!! Thank you so much !!

 

[tutux]

and... just one more question... to display 3 lines with for exemple the 3rd, 4th, 5th last record?

because if i change the values "0,1" after ORDER BY id DESC LIMIT it doesn't work...

 

[troester]

I think the prefilter query will return only one record. If you need a complex query which can't be done with MySql directly use the 'eval' type: put php code in the value field returning the ids needed
...
Return '(5,23,17)';

gesendet mit Tapatalk

 

[tutux]

yes you right, i've gt only one row displayed... The goal was to display an article list in the same page, with 3 differents template
list A with template A
list B with template B
list C with templacte C

B, C are a copy from A list
.

But i need to display the lastest articles in the templates, with arround 5 new articles every days.
So i can't call the id number, because it always changes...

my php is 5.3

 

[troester]

You have to determine the ids you want to display via php. See WIKI cpmmon php tasks.

gesendet mit Tapatalk

 

[startpoint]

I need to create menu link to list and show last 10 added rows (LIMIT 10).
Based on answer #9 I set a prefilter for my menu link whit this settings:
element - id;
condition - equal;
value - Return '(1,2,3,4)';
type - eval;
The result is empty.

How to create a right prefilter?

 

[troester]

You must use condition "IN" (or NOT IN) in this case.

 

[startpoint]

In this way the problem is solved.
I use this code in value field in prefilter:

$db = JFactory::getDBO();
$db->setQuery("SELECT id FROM table_name WHERE status = 1 ORDER BY `id` DESC LIMIT 5");
$db = $db->loadObjectList();
foreach ($db as $row)
    {
      $arr_id[$row->id] = $row->id;
    }
    $arr_id = implode(",",$arr_id);
return $arr_id;

This code get last 5 added id rows.

Thank You, Troester for your suggestion.

 

[cheesegrits]

Why use an eval like that?

Just do ..

WHERE: id
Condition: IN
VALUE: SELECT id FROM table_name WHERE status = 1 ORDER BY id DESC LIMIT 5
Type: query

Much more efficient as it just gets run as a subquery in the main SELECT for the list, rather than running a separate query.

-- hugh

 

[troester]

This is what I thought, too, but it won't do, see post #6
(Maybe there are MySQL versions supporting it meanwhile?)

 

[cheesegrits]

Oh, crap, yeah, forgot your can't have a LIMIT on an ORDER By in a subquery.

Ignore me then.

-- hugh