Create a menu entry showing Random elements from list

[mkainz]

Hi!
Is there a way to create a menu entry that shows random elements from a list where a field in rows contains a specific word?

Example:

I got a list "school" and i need to show only random records that contains in the field "CLASS" the letter "ABC"

Thanks in advice.

 

[troester]

In a Fabrik list module you can do this (enable random records, number of record,prefilter).
If you need it as menu list item I would create a MySQL view on the table and link a Fabrik list to this view.

 

[mkainz]

I got the 1054 unknown error when i use the prefilter on a repeatgroup element of that list...

1054 Unknown column 'lista_7_repeat.foto2' in 'where clause' SQL=SELECT FLOOR(RAND() * COUNT(*) + 1) AS `offset` FROM `lista` WHERE ( lista_7_repeat.foto2 LIKE '%jpg%' )

 

[mkainz]

Is there a way to manual write a query in the prefilters list to avoid this problem? (If yes, what should i write?)

 

[cheesegrits]

Yes. Although we typically don't write custom code and queries for people in Community support, as the only way we put food on the table is through support subs, and if we provide that level of support in Community, there is little incentive for people to give us those food vouchers in exchange for custom tech support.

That said, although it is not the most efficient way of doing it, you can pretty much run any query you want, by using this trick in a pre-filter ...

WHERE
Field: id
Condition: IN
Value: SELECT id FROM your_maintable [... some query which returns a result set of ids, which is a corelated subquery that can use references from the outer (Fabrik) query if need be, also standard placeholders like {$my->id}, etc...]
Type: query

To get a better sense of what is going on, you can enable Fabrik debug (in the global options) and append &fabrikdebug=1 to your list URL, then look at the getData debug output, which will show you the complete query being run.

-- hugh

 

[cheesegrits]

Another useful trick is the "Pre-filter query" option. If you enter a SELECT query there (returning a single row), we run that before the main getData query, and any selected fields returned in it can be referenced in your pre-filters with {$q->foo}.

Probably not relevant in this instance, although you could probably achieve the same effect by creating your list of random ID's in the pre-query, building a comma separated list of ID with "SELECT GROUP_CONCAT(id) AS foo FROM yourmaintable WHERE ...", then use $q->foo in a value in the pre-filter with

Field: id
Condition: IN
Value: ({$q->foo})
Type: no quotes

Although because the pre-query isn't a menu item option, it's noly available on the list itself, you'd have to copy your list, put the pre-query on that, and use the copy in your Random Things menu item. Unless you didn't mind the overhead of running that extra query for every use of the list.

-- hugh

 

[mkainz]

Thank you!