List items not working when joining two tables

vertmonkee

I have a table called games which has the following fields
id, participant_1, participant_2, league_id, game_date

I also have a leagues table with the fields
id, title

And then a participants table with the fields
id, title

In my games module the list items function works fine if I use the following query

$this->db->join('leagues', 'games.league_id = leagues.id', 'left');
$this->db->select('games.id, games.game_date, leagues.title AS league_title');

When I try and join a second table the page does not display any records

$this->db->join('leagues', 'games.league_id = leagues.id', 'left');
$this->db->join('participants p1', 'games.participant_1 = p1.id', 'left');
$this->db->select('games.id, p1.title AS participant_one, games.game_date, leagues.title AS league_title');

I've checked the query and it returns the results I'm expecting which are
id, participant_one, game_date, league_title

Any ideas why the records aren't appearing?

admin

What gets displayed if you do a print_r on the data returned? Also, if you do a $this->db->debug_query(), does the query match what you are expecting?

vertmonkee

I used debug_query and there is a database error. The first couple of queries work as expected but the last query where it limits the number of results is ordering by the wrong column.

Column 'active' in order clause is ambiguous

I've declared my function with

function list_items($limit = NULL, $offset = NULL, $col = 'games.game_date', $order = 'asc')

Then call list items with

$data = parent::list_items($limit, $offset, $col, $order);

But the query comes back with

SELECT games.id, games.participant_1, games.participant_2, DATE_FORMAT(games.game_date, ' %a %e %b %Y') AS Date, leagues.title AS league_title FROM (games) LEFT JOIN leagues ON games.league_id = leagues.id ORDER BY active asc LIMIT 25

Should this be ordering by games.game_date?

admin

The ambiguous error is because one of the other tables has an active column as well and the ORDER BY doesn't know which one to use unless you prefix it with the table name. Perhaps in your list_items method you put in code to say if ($col == 'active') $col = 'my_table.'.$col or something like that.

Also, because the page sorting state is saved in the session in FUEL, you may need to logout and then back in to see the change without the error.

vertmonkee

That's done the job thanks