How do you do Active Record joins on multiple fields

[eluser]peredurabefrog[/eluser]
I want to create the following query using Active Record:

Code:

select
    powers_id,
    laws_id,
    level_1_type,
    level_1_display_type,
    level_1_numid,
    level_1_alphaid,
    level_2_numid,
    level_2_alphaid,
    power_type,
    power_type_desc_en,
    power_type_desc_en,
    affirmation_type,
    affirmation_en,
    affirmation_cy,
    powers.text_en,
    powers.text_cy,
    lawcontents.text_en,
    lawcontents.text_cy
from
    powers
join
    power_types
on
    powers.power_type = power_types.power_types_id
join
    affirmations
on
    powers.affirmation_type = affirmations.affirmations_id
join
    lawcontents
on
    powers.laws_id = lawcontents.idlaws
and
    powers.level_1_type = lawcontents.type
and
    powers.level_1_numid = lawcontents.numid
where
    level_1_type = $level1type
and
    laws_id = $law
order by
    level_1_numid,
    level_1_alphaid,
    level_2_numid,
    level_2_alphaid

I can't find a way of doing the join on multiple fields, though - i.e. the join to lawcontents in the query above.

Putting the whole join in one statement didn't work for me:

Code:

$this->db->join("lawcontents", "powers.laws_id = lawcontents.idlaws and
    powers.level_1_type = lawcontents.type and
    powers.level_1_numid = lawcontents.numid")

The query produced when I tried to do this simply cut off after the first join condition as though the rest of it wasn't there.

Multiple join() calls don't work because they're not recognised as 'AND's. This gives a database error saying the table/alias is ambiguous.

So I'm currently just using the raw query in a $this->db->query(...) call. I'd rather use Active Record more completely, though, if it's possible; but I haven't seen this explained anywhere. But it is possible that my Google skills have deserted me.

Thanks


Peredur

[eluser]Wondering Coder[/eluser]
how about doing it: $this->db->join('lawcontents', 'powers.laws_id = lawcontents.idlaws and
powers.level_1_type = lawcontents.type and
powers.level_1_numid = lawcontents.numid',left)

[eluser]InsiteFX[/eluser]

Code:

$this->db->join('comments', 'comments.id = blogs.id', 'left');

Multiple function calls can be made if you need several joins in one query.

If you need a specific type of JOIN you can specify it via the third parameter of the function. Options are: left, right, outer, inner, left outer, and right outer.

I suggest that you read the CodeIgniter User Guide on Active Record!

InsiteFX

[eluser]peredurabefrog[/eluser]
Thanks for that:

1. I have read the user guide. It only talks about joining on a single condition, which is not what I want. Please see the OP
2. If I make multiple function calls to join() -- as I say in my post -- the result is a database error. Please see the OP for details.

If you can show me where the documentation refers to what I actually want (i.e. multiple join *conditions*, not joins to multiple tables), I'd be very grateful.

I think you have misunderstood what I'm trying to do.

Cheers


Peredur

[eluser]peredurabefrog[/eluser]
[quote author="Wondering Coder" date="1302032977"]how about doing it: $this->db->join('lawcontents', 'powers.laws_id = lawcontents.idlaws and
powers.level_1_type = lawcontents.type and
powers.level_1_numid = lawcontents.numid',left)[/quote]

I take it you're suggesting that I specify a left join. Is that right?

I'm not at work now until tomorrow morning, but I'll try it as soon as I get in.

If this is the solution, I wonder why specifying a left join should be necessary in order for Active Record to take notice of the extra conditions. The 'raw' query works without it and since Active Record is little more than a string builder, you'd think there'd be no difference.

So I'm not hopeful, to be honest, but I'll certainly give it a go and post back how I got on.

Many thanks for the suggestion.

Cheers


Peredur

[eluser]peredurabefrog[/eluser]
[quote author="Wondering Coder" date="1302032977"]how about doing it: $this->db->join('lawcontents', 'powers.laws_id = lawcontents.idlaws and
powers.level_1_type = lawcontents.type and
powers.level_1_numid = lawcontents.numid',left)[/quote]

Unfortunately not. This is the query that gets constructed (showing just the joins):

Code:

SELECT ...
FROM (`powers`)
JOIN `power_types` ON `powers`.`power_type` = `power_types`.`power_types_id`
LEFT JOIN `lawcontents` ON `powers`.`laws_id` = `lawcontents`.`idlaws`
JOIN `affirmations` ON `powers`.`affirmation_type` = `affirmations`.`affirmations_id`
WHERE ...
ORDER BY ...

The call to join() was exactly as you had it, except for single quotes around the word, 'left'.

As you can see, Active Record appears to stop parsing the join conditions after the first one. But thanks for the helpful suggestion.

Cheers


Peredur

[eluser]Wondering Coder[/eluser]
sorry, forgot to put single quotes. I'm using multiple condition in my join using CI and I don't have a problem showing the their results.

[eluser]peredurabefrog[/eluser]
[quote author="Wondering Coder" date="1302096794"]sorry, forgot to put single quotes. I'm using multiple condition in my join using CI and I don't have a problem showing the their results.[/quote]

I tried this:

Code:

->join('lawcontents l1', 'powers.laws_id = l1.idlaws')
->join('lawcontents l2', 'powers.level_1_type = l2.type')
->join('lawcontents l3', 'powers.level_1_numid = l3.numid')

which produced this:

Code:

SELECT ...
FROM (`powers`)
JOIN `power_types` ON `powers`.`power_type` = `power_types`.`power_types_id`
JOIN `lawcontents` l1 ON `powers`.`laws_id` = `l1`.`idlaws`
JOIN `lawcontents` l2 ON `powers`.`level_1_type` = `l2`.`type`
JOIN `lawcontents` l3 ON `powers`.`level_1_numid` = `l3`.`numid`
JOIN `affirmations` ON `powers`.`affirmation_type` = `affirmations`.`affirmations_id`
JOIN `display_types` ON `powers`.`level_1_display_type` = `display_types`.`display_types_id`
WHERE `level_1_type` = ...
AND `powers`.`laws_id` = ...
ORDER BY ...

But this isn't equivalent to multiple ANDed conditions and doesn't bring back the correct results. Although it stops MySQL generating an error.

There has to be something in my setup here that is making Active Record fail to parse the full join conditions. Could you give me an example of what you're doing so that I can try to see what the difference is?

Either way, I'm very grateful for your help.

Cheers


Peredur

[eluser]Wondering Coder[/eluser]
try it like this:

Code:

->join('lawcontents', 'powers.laws_id = idlaws AND powers.level_1_type = type AND powers.level_1_numid = numid','left')

I'm not using my own pc right now so I can't share my work. Maybe later.

[eluser]peredurabefrog[/eluser]
Thanks a lot. I'll give it a go, but I may be a bit of time getting back. No rest for the wicked, I'm afraid, so I may not get another go at this until tomorrow afternoon.

Cheers


Peredur