Help with modifying SQL query

[djgrrr]

I just can't seem to figure this out. Taking the lead from the Google Sitemap plugin... I just want to add a join so that the query pulls all EXTENDED PROPERTIES for each entry. Right now the SQL is:

Code: Select all

'SELECT
entries.id AS id,
entries.title AS title,
'.$sqlnullfunction.'(entries.last_modified,0) AS timestamp_1,
'.$sqlnullfunction.'(MAX(comments.timestamp),0) AS timestamp_2
FROM '.$serendipity['dbPrefix'].'entries entries
LEFT JOIN '.$serendipity['dbPrefix'].'comments comments
ON entries.id = comments.entry_id
WHERE entries.isdraft = \'false\'
GROUP BY entries.id, entries.title, entries.last_modified
ORDER BY entries.id',

I have tried this and a few other things to no avail...

Code: Select all

'SELECT
entries.id AS id,
entries.title AS title,
'.$sqlnullfunction.'(entries.last_modified,0) AS timestamp_1,
'.$sqlnullfunction.'(MAX(comments.timestamp),0) AS timestamp_2
FROM '.$serendipity['dbPrefix'].'entries entries
LEFT JOIN '.$serendipity['dbPrefix'].'comments comments
ON entries.id = comments.entry_id
LEFT JOIN '.$serendipity['dbPrefix'].'entryproperties entryproperties
ON entries.id = entryproperties.entry_id
WHERE entries.isdraft = \'false\'
GROUP BY entries.id, entries.title, entries.last_modified
ORDER BY entries.id',

What am I doing wrong here?

[garvinhicking]

Hi!

The query is meant to return just a singular row for each entry. You would need to denormalize the whole query to fetch multiple entry properties.

The better way is to fetch the properties in a second call. First get all entry IDs you fetched in the first query, then do a "SELECT * FROM entryproperites WHERE entry_id IN (1, 2, 3, ...)" and then you can merge that result with the PHP array of your previous data.

HTH,
Garvin

[djgrrr]

Hmmm, I vaguely have an idea what you mean...

Ha, ha. Would this involve some kind of loop?


I just don't understand why I can't add some kind of additional JOIN to the original query since all tables match on id/entry_id. Is it a performance issue?

Could you give me some further hint as to how to structure the code? Obviously the first query populates an array named $entries...

[garvinhicking]

Hi!

Ha, ha. Would this involve some kind of loop?

Yip.

I just don't understand why I can't add some kind of additional JOIN to the original query since all tables match on id/entry_id. Is it a performance issue?

You should read up on database normalization as well as "average functions" and the "group by" statement.

Imagine you have 3 entryproperties. This means you have 3 rows for one same entryid.

Because the GROUP BY statement flattens the result to have only one row for each entryid, you would only get one of the entryproperties for each entry. Not all properties of a single entry.

You could use GROUP_CONCAT(ep.value) to get all entryproperties in one single column of a single row and joined with "," - but this is hard to use later.

Could you give me some further hint as to how to structure the code? Obviously the first query populates an array named $entries...

Maybe you can try a few things on your own first, I'm quite short on time to chew it up here. It's also some really very basic SQL that you need to wrap your head around, I'm afraid this exceeds the topic of this serendipity specific forum. You might want to check the MySQL documentation or MySQL/PHP usergroups-forums for things like this. It will definitely make things easier for you to code specific for s9y, once you understand more general issues of SQL querying.

It's also not that hard, googling those average functions and checking the MySQL manual should get you kickstarted and equipped with results quickly.

Also check the serendipity_fetchEntries() function inside include/functions_entries.inc.php, this actually does the same: It groups by entry id first, then cycles all values and uses serendipity_fetchEntryProperties() (or someething like that) to fetch the entryporoperties into the array later on.

HTH,
Garvin

[djgrrr]

Imagine you have 3 entryproperties. This means you have 3 rows for one same entryid.

Yes, this was definitely my logical oversight.


I will "wrap my head" around this and come up with a solution.


Thanks again for your guidance.


James

[djgrrr]

Well, my solution involved simply utilizing the

serendipity_fetchEntryData();

function so that I would have access to the extended properties.


My next problem was accessing the URL for an entry which I finally realized was generated dynamically with the

serendipity_getPermalink();

function.


I was then able to create a loop which limited the output to my selected category and provided all ep data I needed, etc.


So far I'm where I want to be...