Relationships in Query Module

October 12, 2017 11:38am

Subscribe [1]
  • #1 / Oct 12, 2017 11:38am


    10 posts

    Hi guys,

    I cannot finish my SQL query with relationship fields. This is my code:

    {exp:query sql="
    SELECT DISTINCT exp_channel_data.field_id_97 AS title, exp_channel_data.field_id_98 AS id, exp_channel_data.field_id_399 AS ratio, exp_channel_data.field_id_160 AS fullscreen_mode,
      REPLACE (exp_channel_data.field_id_454, '\\\\{filedir_29\\\\}', '') AS image
      FROM exp_channel_titles
      JOIN exp_channel_data ON exp_channel_data.entry_id = exp_channel_titles.entry_id
      JOIN exp_category_posts ON exp_category_posts.entry_id = exp_channel_titles.entry_id
      JOIN exp_relationships ON exp_relationships.child_id= exp_channel_titles.entry_id 
      WHERE exp_channel_titles.channel_id='34' AND exp_relationships.field_id = 455 AND(expiration_date = '0' OR expiration_date > UNIX_TIMESTAMP(NOW())) AND exp_category_posts.cat_id = '{embed:cat}' AND status = 'open'
      ORDER BY exp_channel_titles.entry_date
      <a href="#" class="class2">/images/uploads/example/{image}<div class="winner"><span class="title">{title}</span><span class="js3"></span></div></a>

    I want to show some entries (status = open, embed category, channel id) but base on relationship field.

    Code above doesn’t work. When I replace the line with WHERE using this: “WHERE exp_relationships.field_id = 455” then finally I got all entries related to the field 455 but I want to also filter them by status, category and channel.

    How can I do it if I have parent filed in one channel and 4-5 fields (children) in another channel?

    Thanks for your help!

ExpressionEngine News

#eecms, #events, #releases