Welcome Guest, Not a member yet? Register   Sign In
Require help with a couple LEFT JOIN's
#1

[eluser]kaisama[/eluser]
Hello all :x As I'm porting my current website over to using CodeIgniter, I have run into a small dilemma. Through all my tries, I cannot "translate" my original MySQL query call over to CI's method.

My Original Query:
Code:
$active_members_sql = "

SELECT akira_artists_bands.stage_name_romaji, akira_artists_bands.stage_name_kana, akira_artists_bands.position_id, akira_artists_positions.position

FROM akira_artists_bands

LEFT JOIN akira_artists_positions
ON akira_artists_bands.band_id=$band_id AND akira_artists_bands.position_id=akira_artists_positions.id

WHERE akira_artists_bands.band_id=$band_id AND
akira_artists_bands.status = 'Active' AND
akira_artists_bands.support = '0'

ORDER BY akira_artists_positions.sort ASC

";

I put the spaces in it to make it a little easier to read. How I love CI's clean code method of handling this @@;

I can do everything in CI except the LEFT JOINs.

Current CI Code:
Code:
$akira_db->from('akira_artists_bands');
$akira_db->join('akira_artists_positions', array('band_id' => $band_id), 'left');
$akira_db->join('akira_artists_positions', 'position_id = position_id', 'left');
$akira_db->where('band_id', $band_id);
$akira_db->where('status', $status);
$akira_db->where('solo_project', $solo);

I can get it to work without errors if I omit line 2 above (the first LEFT JOIN call), but of course, it doesn't pull the right data that I need.

I'm also not sure how to do my ORDER BY in CodeIgniter.

I hope I explained everything clearly. Any help on this would be awesome. It's driving me crazy.

Thanks in advance~
#2

[eluser]alboyd[/eluser]
Just do this?
Code:
$active_members_sql = "

SELECT akira_artists_bands.stage_name_romaji, akira_artists_bands.stage_name_kana, akira_artists_bands.position_id, akira_artists_positions.position

FROM akira_artists_bands

LEFT JOIN akira_artists_positions
ON akira_artists_bands.band_id= ? AND akira_artists_bands.position_id=akira_artists_positions.id

WHERE akira_artists_bands.band_id= ? AND
akira_artists_bands.status = 'Active' AND
akira_artists_bands.support = '0'

ORDER BY akira_artists_positions.sort ASC";

$result = $this->db->query($active_members_sql, array($band_id, $band_id));

Problem solved! You don't have to use that funny old activerecord thingo you know? Much simpler to just write your queries!
#3

[eluser]kaisama[/eluser]
Hm. I'd rather use the active record method. It's cleaner. But I suppose I could use that method for now until I figure it out :x thanks.
#4

[eluser]Fielder[/eluser]
I would get rid of the array('band_id' => $band_id) embedded in your statement, and just assign that value to a var before you go ino your $akira_db->join.
Also, you probably need to be more explicit everywhere in your statement. For instance...
Code:
$akira_db->join('akira_artists_positions', 'akira_artists_positions.position_id = akira_artists_bands.position_id', 'left');

Orderby is easy
Code:
$this->db->order_by('akira_artists_positions.sort', 'asc');


All my code is written like this.
Code:
$this->db->order_by('store.store_id','desc');
unlike yours without the $this->. Not familiar with your way, but I suppose it's just a different approach.
#5

[eluser]kaisama[/eluser]
Oo, thank you Fielder. I got it working Smile

Final Code:
Code:
$akira_db->from('akira_artists_bands');
$akira_db->join('akira_artists_positions', 'akira_artists_bands.position_id = akira_artists_positions.id', 'left');
$akira_db->where('akira_artists_bands.band_id', $band_id);
$akira_db->where('akira_artists_bands.status', $status);
$akira_db->where('akira_artists_bands.support', $support);
$akira_db->order_by('akira_artists_positions.sort','asc');

And as far as why I'm using $akira_db-> instead of $this->, is because I am working with two databases (main database and forum database), and manually call each database for my models, assigning them to arrays to use. Explains it here at the bottom of the page:

Connecting to your Database : CodeIgniter User Guide

Code:
$akira_db = $this->load->database('akira', TRUE);
$forum_db = $this->load->database('forum', TRUE);

Though, I don't know if I can, but I wish I could auto assign the databases once instead of having to do it each time in my models/whatever. I have the database library auto load, of course.
#6

[eluser]Fielder[/eluser]
I do it the same way as you at the top of each of my model files, but have never tried moving them.

I bet you could do this...
Code:
$autoload['model'] = array('akira','forum');

in your config/autoload.php file.
If you try it, let me know. I'd be interested in optimizing my code myself this way.




Theme © iAndrew 2016 - Forum software by © MyBB