complicated sql query

[eluser]JanDoToDo[/eluser]
hey guys.

I have a long sql query currently written using the AR class.

However, I need to add a table join and need a case/If statement in there. Is there anyway to do this without rewriting the AR code into a normal SQL statement?

I need this :


$this -> db -> join($this -> tables['blacklist'], 'ua.user_id = CASE WHEN b.user_id = 0 THEN b.blacklist_id END', 'LEFT');

rewritten as a working statement. Obvisouly the problem is that it can't do it as it currently is, but can i just add this some other way.
This is the current code.

Code:

$this -> db -> select($this -> col_lists['basic'] . ', ua.verified, b.blacklist_id AS on_blacklist, ' . $this -> col_lists['uni_list'] . ', ' . $this -> col_lists['uni_years']);
$this -> db -> from($this -> tables['basic']);
$this -> db -> join($this -> tables['account'], 'ub.user_id = ua.user_id', 'LEFT');
$this -> db -> join($this -> tables['blacklist'], 'ua.user_id = CASE WHEN b.user_id = 0 THEN b.blacklist_id END', 'LEFT');
        
$this -> db -> where_in('un.region', $regions);
$this -> db -> where('ub.user_id !=', $this -> session -> userdata('user_id'));
$this -> db -> order_by($sorting);
$this -> db -> limit($per_page, $result_start);

[eluser]Dan Horrigan[/eluser]
Short answer: You would have to take it out of AR and write it by hand.

Longer answer: The issue is the CASE statement. When it runs through the AR class it becomes this:

Code:

`CASE` WHEN b.user_id = 0 THEN b.blacklist_id END

Notice it escapes the CASE word.

You could get around this by using an IF() instead. Here is an example:

Code:

$this -> db -> join('blacklist', 'ua.user_id = IF(b.user_id = 0, b.blacklist_id, NULL)', 'LEFT');

This code will execute correctly. You will obviously need to change the 3rd parameter to what user_id needs to be set to if b.user_id is not 0.

Hope this helps.

Dan

[eluser]JanDoToDo[/eluser]
Dan,

Works perfectly. Just changed the CASE statement for an IF. Do you know, or are you able to point me to any resources that explains the difference? Performance, or just syntax?

[eluser]Dan Horrigan[/eluser]
Good to here that worked. The best place to look is the MySQL Manual.

http://dev.mysql.com/doc/refman/5.0/en/c...tions.html

Dan