SQL Query with related entries

#1 / Sep 24, 2012 8:01am
amadeus77
14 posts
I want to achieve the following:

I need the title and one more custom field from a channel_entry that is related to the channel-entry that I’m actually viewing… I can’t use expression engine tags and only have the segment_3 as unique variable for getting the related entry through the query module. Here is what I have until now:
```
SELECT exp_channel_data.field_id_11, exp_channel_titles.title
FROM exp_channel_titles
JOIN exp_channel_data ON exp_channel_data.entry_id = exp_channel_titles.entry_id
JOIN exp_relationships ON exp_channel_data.entry_id = exp_relationships.rel_child_id
WHERE exp_channel_titles.url_title = '{segment_3}'
```
I’m getting data but the wrong one from any other channel! What is wrong?

#2 / Sep 25, 2012 9:49am

Dan Decker

7338 posts

Hi amadeus77,

I can’t use expression engine tags…

Why not? This would be simple enough using {exp:channel:entries} and {related_entries}

{exp:channel:entries channel="news" url_title="{segment_3}"}
    <h2>{title}</h2>
<p>    {custom_field}<br />
{related_entries id="related_field"}<br />
...<br />
{/related_entries}<br />
{/exp:channel:entries}

But if you really need the SQL, try this:

SELECT exp_channel_data.field_id_11, exp_channel_titles.title
FROM exp_channel_titles
JOIN exp_channel_data ON exp_channel_data.entry_id = exp_channel_titles.entry_id
JOIN exp_relationships ON exp_channel_data.entry_id = exp_relationships.rel_child_id
WHERE exp_channel_titles.url_title = {segment_3}

Two things: It would be most helpful to see your entire template and, make sure to turn on template debugging so that you can see how the query module is executing your query. Admin-> System Administration-> Output and Debugging

Cheers,

#3 / Sep 25, 2012 10:16am

amadeus77

14 posts

I can’t use them because I’m using “ShinePdf” an addon for creating PDFs on the fly from channel entries… I need to put data from a related channel into the pdf, but embeding or anything else is not possible within the addon tags…

My code: Maybe you’ll see, what I’m trying to achieve…

{exp:query 
sql="
SELECT exp_channel_data.field_id_11, exp_channel_titles.title
FROM exp_channel_titles
JOIN exp_channel_data ON exp_channel_data.entry_id = exp_channel_titles.entry_id
JOIN exp_relationships ON exp_channel_data.entry_id = exp_relationships.rel_child_id
WHERE exp_channel_titles.url_title = '{segment_2}'
"}
<?php 
$test= "{title}";
?>        
{/exp:query}

{!--
{exp:shine_pdf:make 
 channel="jobs" 
    url_title="{segment_2}"
    format="A4"
    default_font_size="11"
    default_font="Helvetica"
    margin_left="15"
    margin_right="15"
    margin_top="16"
    margin_bottom="16"
    margin_header="9"
    margin_footer="9"
    orientation="p"
    margin_top_auto="no"
    margin_bottom_auto="no"
}
<style>
p, h2, li {
 color:#666;
}
</style>
images/interface/qunit_logo_pdf.jpg
  <h2>{jobs_bezeichnung}</h2>
<p>{jobs_text}<br />
<?php echo $test ?><br />
{/exp:shine_pdf:make}<br />
--}

With your Code and Template-Debugging turned on, I’m getting this right sql-syntax:

(0.007550 / 4.35MB) Tag: {exp:query sql=" SELECT exp_channel_data.field_id_11, exp_channel_titles.title FROM exp_channel_titles JOIN exp_channel_data ON exp_channel_data.entry_id = exp_channel_titles.entry_id JOIN exp_relationships ON exp_channel_data.entry_id = exp_relationships.rel_child_id WHERE exp_channel_titles.url_title = 'teamassistentin_m_w_chemiekonzern' "}

But I’m not getting any results… but there ARE entries! Maybe this is not getting the right Query for getting a related record?

#4 / Sep 26, 2012 8:50am

amadeus77
14 posts

I’d really would like to go ahead with this project and it would be really great to here something from you. This is the first time I’m really not coming forward without your help.

#5 / Sep 26, 2012 10:33am

Dan Decker

7338 posts

Hey amadeus77,

I’d really would like to go ahead with this project and it would be really great to here something from you.

All apologies for your delays, we do our best to reply within 2 business days.

You need your ShinePDF code inside your query tags. In your example, you have the Shine code commented out, so ExpressionEngine isn’t going to do anything with it. You also might need to cast some of the variables in your query:

{exp:query 
sql="
SELECT exp_channel_data.field_id_11 AS some_name, exp_channel_titles.title AS title
FROM exp_channel_titles
JOIN exp_channel_data ON exp_channel_data.entry_id = exp_channel_titles.entry_id
JOIN exp_relationships ON exp_channel_data.entry_id = exp_relationships.rel_child_id
WHERE exp_channel_titles.url_title = '{segment_2}'
"}
{exp:shine_pdf:make 
 channel="jobs" 
    url_title="{segment_2}"
    format="A4"
    default_font_size="11"
    default_font="Helvetica"
    margin_left="15"
    margin_right="15"
    margin_top="16"
    margin_bottom="16"
    margin_header="9"
    margin_footer="9"
    orientation="p"
    margin_top_auto="no"
    margin_bottom_auto="no"
}
<style>
p, h2, li {
 color:#666;
}
</style>
images/interface/qunit_logo_pdf.jpg
  <h2>{jobs_bezeichnung}</h2>
<p>{jobs_text}<br />
{title} - {some_name}<br />
{/exp:shine_pdf:make}{/exp:query}

That should be closer to the mark.

Cheers,

#6 / Oct 01, 2012 9:36am
amadeus77
14 posts
Hey Dan,

I just commented out the shine code so the client does not see false results in the preview version. Your tip to put the shine code inside the query tags was one thing… I knew that, but just didn’t see it ... *grrr 😉

But also the query had to look totally different at the end. Here is my result for getting data from an related entry depending on the url_title within segment_2 in the URL (from master entry):
```
SELECT contacts.title AS contact_title, contacts_data.field_id_11 AS contact_data
FROM exp_channel_titles
JOIN exp_relationships ON exp_channel_titles.entry_id = exp_relationships.rel_parent_id
JOIN exp_channel_titles AS contacts ON contacts.entry_id = exp_relationships.rel_child_id
JOIN exp_channel_data AS contacts_data ON contacts.entry_id = contacts_data.entry_id
WHERE exp_channel_titles.url_title = '{segment_2}'
```
Cheers.
#7 / Oct 02, 2012 9:56am

Dan Decker
7338 posts

Hi amadeus77,

Ahh, excellent. It looks like you’ve gotten a solution, correct?

Sometimes it takes a extra set of peepers to catch all the gotchas.

Is there anything else I can assist you with?

Cheers,
#8 / Oct 04, 2012 2:54am

amadeus77
14 posts

No, everythings fine. You can close the thread 😊 Thank you.
#9 / Oct 05, 2012 2:32pm

Dan Decker
7338 posts

Excellent!

If you need anything else, let me know!

Cheers,

Thread

amadeus77

Dan Decker

amadeus77

amadeus77

Dan Decker

amadeus77

Dan Decker

amadeus77

Dan Decker

Username

Password

Thread