• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Active Record Join with and or

#1
Hi all ,

As you see in the code below im trying to select only the messages marked active (flagged with a 1.
i have joins to other tables like friends and account or avatar. all works great exept not for the $where_active.

Code:
$user_id  = $this->auth->get_user_id();
       $result = array();
       $where = "user_message.tmln_user_id = $user_id OR user_friends.fred_one_id = $user_id OR user_friends.fred_two_id = $user_id ";
       $where_relation = "user_friends.fred_relation = '1'";
       $where_active = "user_message.tmln_active = '1'";
       $this->db->select(' user_accounts.uacc_id,
                           user_accounts.uacc_username,
                           user_avatar.avta_uacc_fk,
                           user_avatar.avta_img_name,
                           user_message.tmln_id,
                           user_message.tmln_user_id,
                           user_message.tmln_message,
                           user_message.tmln_date_added,
                           user_message.tmln_active,                            
                           user_friends.fred_one_id,
                           user_friends.fred_two_id,
                           user_friends.fred_relation');
       $this->db->from('user_message') ;        
       $this->db->join('user_friends', 'user_message.tmln_user_id = user_friends.fred_one_id OR user_message.tmln_user_id = user_friends.fred_two_id','left');
       $this->db->join('user_avatar', 'user_avatar.avta_uacc_fk = user_message.tmln_user_id','left');
       $this->db->join('user_accounts', 'user_accounts.uacc_id = user_message.tmln_user_id','left');
       $this->db->group_by ('user_message.tmln_date_added');
       $this->db->order_by ('user_message.tmln_date_added','desc');
       $this->db->where($where_active);
       $this->db->where($where);
       $this->db->where($where_relation);
       
       $result = $this->db->get();
       if ($result->num_rows() > 0):
           return $result->result();
       else:
           return false;
       endif;


thanks for your help
Reply

#2
If I remember correct the order_by should be the last statement after the where.

Mysql does group by before order by and you get mixed results not what you expected.

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]


In general, clauses used must be given in exactly the order shown in the syntax description. For example,
a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause. The exception is that
the INTO clause can appear either as shown in the syntax description or immediately following the select_expr list.
What did you Try? What did you Get? What did you Expect?

Joined the CodeIgniter Community in 2009.          ( Skype: insitfx )
Reply

#3
(05-02-2017, 05:03 AM)InsiteFX Wrote: If I remember correct the order_by should be the last statement after the where.

Mysql does group by before order by and you get mixed results not what you expected.

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]


In general, clauses used must be given in exactly the order shown in the syntax description. For example,
a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause. The exception is that
the INTO clause can appear either as shown in the syntax description or immediately following the select_expr list.

thanks for the reply unfortunatly record with tmln.active = 0 still show up Sad

got it to work by replaceing.
$this->db->where($where_active);
with
$this->db->having($where_active);
Reply

#4
Instead of $this->db->get(), try $this->db->get_compiled_select() and output the actual query that CodeIgniter will use. It helps debugging when there are issues with the Query Builder. My initial guess is an issue with the WHERE clauses. Why do you have three separate calls to ->where() when you are compiling them into variables at the top anyway? Why not combine them?

The order is less relevant in regards to the different methods as the query isn't built until the end where it will place the items in the correct order (WHERE before GROUP BY for example). For legibility sake, I would keep it as close as possible.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2017 MyBB Group.