Serendipity Forums

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

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

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 !

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.

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.

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

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'));