How to use filter_sql ?

[jdrodrigues]

Hi there,

Could anyone provide any examples of what can be included (& how) in the "$filter_sql" option for the function serendipity_fetchEntries ?

Can I include an elaborate SQL query such as:


SELECT
serendipity_entries.id
FROM serendipity_entryproperties, serendipity_entries, serendipity_permalinks,
serendipity_category, serendipity_entrycat
WHERE serendipity_entryproperties.value = '$cid'
AND serendipity_entryproperties.property = 'ep_Info_Source_IDref'
AND serendipity_entries.id = serendipity_entryproperties.entryid
AND serendipity_entries.isdraft = 'false'
AND serendipity_permalinks.entry_id = serendipity_entryproperties.entryid
AND serendipity_permalinks.type = 'entry'
AND serendipity_entrycat.entryid = serendipity_entryproperties.entryid
AND serendipity_category.categoryid = serendipity_entrycat.categoryid
ORDER BY serendipity_entries.timestamp DESC LIMIT $offset, $perpage

(Where $cid, $perpage, & $offset are variables.)

Thank you!
Joel

[garvinhicking]

Hi!

The fetchEntries function is aimed at returning entries and contents; your SQL query would only fetch an entry id, it is very specific.

I suggest you to use that query directly in your code/plugin and use the serendipity_db_query() function for that.

$filter_sql is only meant to contain additional "WHERE" conditions. For other conditions, only plugin API hooks are available.

Regards,
Garvin

[jdrodrigues]

Hi Garvin,

Sorry about that, the SQL I quoted is what I used to test getting entries out without using any Serendipity API.

So can I just use the "Where" bit ?

WHERE serendipity_entryproperties.value = '$cid'
AND serendipity_entryproperties.property = 'ep_Info_Source_IDref'
AND serendipity_entries.id = serendipity_entryproperties.entryid
AND serendipity_entrycat.entryid = serendipity_entryproperties.entryid
AND serendipity_category.categoryid = serendipity_entrycat.categoryid


How exactly would I go about using it ?

Thanks again Garvin !

[jdrodrigues]

My test.php page :

<?php
chdir('../');
ob_start();

include_once('serendipity_config.inc.php');

serendipity_smarty_init();

$sql = "serendipity_entries.id <= 100";

serendipity_printEntries(serendipity_fetchEntries(null, true, $serendipity['fetchLimit'], $filter_sql = $sql));

$serendipity['smarty']->display(serendipity_getTemplateFile('entries.tpl', 'serendipityPath'));

?>

But, it doesn't seem to do anything...

Help ?... please.

[jdrodrigues]

ok, I figured it out. Talk about undocumented software

$sql = "serendipity_entryproperties.value= '226'";

serendipity_printEntries(serendipity_fetchEntries(null, true, $serendipity['fetchLimit'], false, false, 'timestamp DESC', $filter_sql = $sql));



But, it complains :
"Unknown table 'serendipity_entryproperties' in where clause"

Where & how do I add additional tables into the where clause ?

Thank you.

[garvinhicking]

Hi!

Yes, you should best look at the SQL query generated in fetchEntries and debug output that one.

Entryproperties is aliased as "ep" in the query.

You will need to write a plugin that hooks into the SQL query statement hooks to add additional clauses.

Best regards,
Garvin

[jdrodrigues]

Hi,

I resolved the issue using the following code. Essentially using my own PHP & SQL code to extract the entries I need and then using existing s9y functions to print them.

$query = "SELECT DISTINCT
serendipity_entries.id
FROM serendipity_entryproperties, serendipity_entries, serendipity_permalinks, serendipity_category, serendipity_entrycat
WHERE serendipity_entryproperties.value = '$cid'
AND serendipity_entryproperties.property = 'ep_Info_Source_IDref'
AND serendipity_entries.id = serendipity_entryproperties.entryid
AND serendipity_entries.isdraft = 'false'
AND serendipity_permalinks.entry_id = serendipity_entryproperties.entryid
AND serendipity_permalinks.type = 'entry'
AND serendipity_entrycat.entryid = serendipity_entryproperties.entryid
AND serendipity_category.categoryid = serendipity_entrycat.categoryid
ORDER BY serendipity_entries.timestamp DESC, serendipity_entries.title ASC LIMIT $offset, $perpage";

$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

while ($row = mysql_fetch_array($result)) {

$entryid = $row[0];

$sqlfilter= "id = '$entryid'";

serendipity_printEntries(serendipity_fetchEntries(null, true, $serendipity['fetchLimit'], false, false, 'timestamp DESC', $filter_sql = $sqlfilter));

$serendipity['smarty']->display(serendipity_getTemplateFile('mycustomtemplate.tpl', 'serendipityPath'));