Distinct field results

[vlatkoIT]

Hi, You are doing great work updating and maintaining the code. One thing that I currently need is not covered (as far as I know) or maybe I don't really know how to use the core $this->db->distinct() with this model. I intend to take one post per user and limit to 40 user posts. Now it looks like this: $this->group_by('user_id')->distinct()->where('publish <=',date("Y-m-d H:i:s"))->order_by('publish','ASC')->limit(40)->get_all(); I took the logic from http://stackoverflow.com/questions/19509805/how-to-use-distinct-here-in-my-query-codeigniter, so how can I possibly acquire these distinct results. Thank you for any possible answer and great respect for what has been done until now. Regards

[salain]

Hi, You have 2 ways to do this: 1st option:

$this->db->distinct();
 $this->group_by('user_id')->where('publish <=',date("Y-m-d H:i:s"))
->order_by('publish','ASC')->limit(40)->get_all();

2nd option: is to modify the magic function __call() in MY_Model as suggested in issue#74

[vlatkoIT]

Thank you salain, I would rather use the first option, because I need fast solution. Your solution works, but unfortunately group_by gets executed before order_by, so I get distinct but not properly ordered solutions. I tried to find a way to use subquery, but till now I am not succeeding in that. I tried few solutions with subquery, no success. Any thoughts on that.

[salain]

Hi @dandruff123 ,

The only way I can think of to get this to work is to use an ordered subquery as the table. You can do it using CI query:

$query = $this->db->query('SELECT * 
   FROM (SELECT * FROM posts ORDER BY publish ASC ) AS table1 
  WHERE ('publish <=' . date("Y-m-d H:i:s"). ' GROUP BY user_id  LIMIT 40');

OR if you want to use the QB with MY_Model, you have to temporarily change the table to a subquery something like this:

$temp_table = $this->table; // save table name to restore. this may not be needed
$this->table = '(SELECT * FROM posts ORDER BY publish ASC ) AS table1';
$this->db->distinct();  // Should not be needed
$records = $this->group_by('user_id')
     ->where('publish <=',date("Y-m-d H:i:s"))
     ->limit(40)
     ->get_all();
$this->table = $temp_table; //Restore table.  Same as above may not be needed
return $records;

[salain]

@dandruff123

You could also try this library https://github.com/NTICompass/CodeIgniter-Subqueries

It should work with CI 3.X out of the box.

[jonasbraga]

If someone still needs a way to select distinct fields with this MY_MODEL class here is what I did

$this->model->fields()->select('DISTINCT field_1, field_2', FALSE)->get_all()

what compiled creates a sql

SELECT DISTINCT field_1, field_2 FROM table

the trick here is the fields() method, which must be set null, otherwise the sql will be

SELECT DISTINCT field_1, field_2, * FROM table

and it throws a sql sintax error;