Plugin

Archived
Forum
(read-only)


For official support, visit the official support site »

Active Record

ExpressionEngine 2

Back to this add-on's main page
View Other Add-ons From Rob Sanchez

Active Record  >  Thread
     

Joins

General

Jacek Sompel
Posted: 19 March 2011 12:29 AM
Jacek Sompel

How do you do multiple Joins?

I tried to do some thing like this:

{exp:activerecord
        select="entry_id, title, url_title, comment_total, field_id_17 as article_excerpt, view_count_one, screen_name as author"
        from="exp_channel_titles"
            join="exp_members"
            on="exp_members.member_id = exp_channel_titles.author_id"
            join_type="inner"
            join="exp_channel_data"
            on="exp_channel_data.entry_id = exp_channel_titles.entry_id"
            join_type="left"
            join="exp_relationships"
            on="exp_relationships.rel_id = exp_channel_data.field_id_19"
            join_type="left"
        where:rel_child_id="{entry_id}"
        order_by="entry_date DESC"
        limit="10"
        paginate="bottom"
    } 

Only the last join will ever be used.

Generated SQL:

SELECT `entry_id`, `title`, `url_title`, `comment_total`, `exp_channel_data`.`field_id_17` as article_excerpt, `view_count_one`, `screen_name` as author FROM (`exp_channel_titles`) LEFT JOIN `exp_relationships` ON `exp_relationships`.`rel_id` = `exp_channel_data`.`field_id_19` WHERE `rel_child_id` = '593' ORDER BY `entry_date` DESC LIMIT 10 

Dumped the $this->EE->TMPL->tagparams and this is the output:

Array
(
    [select] => entry_id, title, url_title, comment_total, exp_channel_data.field_id_17 as article_excerpt, view_count_one, screen_name as author
    [from] => exp_channel_titles
    [join] => exp_relationships
    [on] => exp_relationships.rel_id = exp_channel_data.field_id_19
    [join_type] => left
    [where:rel_child_id] => 593
    [order_by] => entry_date DESC
    [limit] => 10
    [paginate] => bottom
) 

It’s either I am doing something wrong or this plugin doesn’t support multiple joins like normal Active Record does.

Any ideas?
 
Rob Sanchez
Posted: 21 March 2011 10:23 PM # 1
Developer
Rob Sanchez

EE doesn’t allow you to use a parameter name more than once. That’s why this doesn’t work. I can think about adding an alternate syntax for using multiple joins.
 
Jacek Sompel
Posted: 21 March 2011 11:19 PM # 2
Jacek Sompel

Perhaps it would be as simple as join_1, join_type_1, on_1, join_2, join_type_2, on_2 etc.
That way in the plugin you simply parse these parameter sets.

Alternative could be join = “join table 1 | join table 2”, join_type = “inner | left”, on = “field 1 = field 2 | field 3 = field 1”. then in the plugin code $joins = preg_split(’/\|/’, $join);. Even if the user selects a single join it still returns an array which then can be parsed.

Both ways would work just fine. Second method would most likely be a bit more flexible as in theory it allows unlimited joins with only 3 parameters.
 
Rob Sanchez
Posted: 22 March 2011 12:03 AM # 3
Developer
Rob Sanchez

It will likely be the same as the multiple where syntax, join, join etc.
 
adisys
Posted: 21 October 2011 10:11 AM # 4
adisys

Is this implemented?
 
Rob Sanchez
Posted: 23 October 2011 01:20 PM # 5
Developer
Rob Sanchez

Not implemented, I haven’t had time and kinda forgot about it, sorry. No ETA for now. It would help if you posted this on github as an issue there, since I don’t really follow these forums.
 
 
← or where? et al Installing ActiveRecord freezes Plugin Manager →