› ›
CRITICAL SECURITY VERSION UPDATE:
https://github.com/daylightstudio/FUEL-CMS/releases/tag/1.4.13

List items not working when joining two tables

vertmonkeevertmonkee
edited September 2011 in Modules
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?

Comments

  • adminadmin
    edited 11:33AM
    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?
  • vertmonkeevertmonkee
    edited 11:33AM
    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?
  • adminadmin
    edited 11:33AM
    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.
  • vertmonkeevertmonkee
    edited 11:33AM
    That's done the job thanks
Sign In or Register to comment.