Hello,
I a getting some really odd behavior when using a custom query. The query below works fine 90% of the time, but every so often it seems to completely skip over one of the results its supposed to return. The weirdest thing is that simply going to the skipped entry, editing it, and then hitting submit fixes the issue, even if nothing on the entry was changed.
Im not so much concerned with troubleshooting the query, rather I just want to understand what could be happening when I resubmit an entry that would change the results of the query? As far as I dont know I dont have any caching on that would affect this. Its as if EE doesnt realize that the entry exists until I resubmit it to the database. Any ideas?
I have put the query below, in case anyone would like to look at it.
{exp:query sql="SELECT
st.title AS song_title,
YEAR(FROM_UNIXTIME(st.entry_date)) AS song_entry_year,
at.title AS artist_name,
IF(sd.field_id_186 != '', CONCAT('/pics-tracks/', sd.field_id_186), CONCAT('/pics-artist/', ad.field_id_185)) AS square_image,
IF(sd.field_id_271 != '', sd.field_id_271, IF(sd.field_id_270 != '', sd.field_id_270, sd.field_id_19)) AS source,
IF(sd.field_id_271 != '', 'youtube', IF(sd.field_id_270 != '', 'soundcloud', 'mp3')) AS type,
sd.field_id_23 AS song_description,
sd.field_id_196 AS song_itunes,
sd.field_id_199 AS song_download,
sd.field_id_17 AS song_producer,
sd.field_id_13 AS song_name,
st.url_title AS song_url,
at.title AS artist_name,
at.url_title AS artist_url
FROM
exp_playa_relationships AS r1 JOIN
exp_channel_titles AS st ON r1.child_entry_id = st.entry_id JOIN
exp_channel_data AS sd ON st.entry_id = sd.entry_id JOIN
exp_playa_relationships AS r2 ON st.entry_id = r2.parent_entry_id JOIN
exp_channel_titles AS at ON r2.child_entry_id = at.entry_id JOIN
exp_channel_data AS ad ON at.entry_id = ad.entry_id
WHERE
r1.parent_entry_id = {entry_id} AND
r1.parent_field_id = 295 AND
r2.parent_field_id = 96 AND
r2.rel_order = 0"}
{song_title} - {source} {type}
{/exp:query}