MySQL query into Code Igniter Active Directory format?

Question

I'm trying to get mysql query into Code Igniter's Active Record syntax but am having a bit of a hard time.

The query is as a result of this question: Multiple mysql ORDER BY's for multidimensional ordering/grouping

I've attempted to format the query myself, have tackled a couple of errors, but am unsure how to progress. I had to add in the get_compiled_select() function to DB_active_rec.php myself and change the _reset_select() from protected to public to get it to run at all.

The suggested query is:

select
  t.id,
  t.group,
  t.date,
  t.comlete
from
  YourTable t
  left join
    (select
      m.group,
      min(m.date) as mindate,
      min(t.complete) as groupcomplete
    from
      YourTable m) mt on mt.group = t.group
order by
  coalesce(mt.groupcomplete, t.complete),
  coalesce(mt.mindate, t.date),
  t.group,
  t.complete,
  t.date

My translation looks like this (note that there's a 'where' clause not in the original, and that 'date' is actually 'due'):

        // Sub query
        $this->db->select('m.group, min(m.due) as mindate, min(t.complete) as groupcomplete');
        $this->db->from('task m');
        $this->db->where('property', $property);

        $subquery = $this->db->get_compiled_select();

        $this->db->_reset_select();

        // Main query
        $this->db->select('*');
        $this->db->where('property', $property);
        $this->db->from('task t');
        $this->db->join('($subquery) mt','mt.group = t.group');

        $this->db->order_by('coalesce(mt.groupcomplete, t.complete)');
        $this->db->order_by('coalesce(mt.mindate, t.due)');
        $this->db->order_by('t.group');
        $this->db->order_by('t.complete');
        $this->db->order_by('t.due');

        $query = $this -> db -> get();

        // Return
        return $query->result();

Unfortunately this is just throwing an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mt ON `mt`.`group` = `t`.`group` WHERE `property` = '7' ORDER BY coalesce(mt.gr' at line 3

The query as reported by CI looks like:

SELECT * FROM (`task` t) JOIN ($subquery) mt ON `mt`.`group` = `t`.`group` WHERE `property` = '7' ORDER BY coalesce(mt.groupcomplete, `t`.`complete)`, coalesce(mt.mindate, `t`.`date)`, `t`.`due`, `t`.`complete`, `t`.`date`

Anyone able to lend some advice as to how to get this formatted correctly? My mysql skills are, unfortunately, pretty bare, so this is pushing my abilities. Much of the approach of my translation is from answers on Stack Overflow, as I have no experience combining queries in this way (with the subquery).

try using $this->db->last_query() to see what's wrong with your query — Drixson Oseña
– Drixson Oseña, Commented Nov 12, 2013 at 14:12
Hi Drixson, The error is already reporting (as noted at the bottom of the question). I did stick that function call in, but it didn't make any difference to the error reporting. Do I need to call it somewhere more specific? — Nathan Hornby
– Nathan Hornby, Commented Nov 12, 2013 at 14:15
see this ('task' t) I think it should be like ('task') t anyways, try replacing t as task — Drixson Oseña
– Drixson Oseña, Commented Nov 12, 2013 at 14:22
If my hunch is right I encountered this problem and was related to $this->db->select('*') — Drixson Oseña
– Drixson Oseña, Commented Nov 12, 2013 at 14:23
@altsyset For future reference that backtick issue is a known bug in CI - github.com/EllisLab/CodeIgniter/issues/296 Whitespace fixes it. — Nathan Hornby
– Nathan Hornby, Commented Nov 12, 2013 at 15:07

GolezTrol · Accepted Answer · 2013-11-12 14:34:17Z

1

The problem (or 'one of the problems') is here:

$this->db->join('($subquery) mt','mt.group = t.group');

You use single quotes, so the variable $subquery doesn't get expanded. This can also be seen in the query that is outputted by CodeIgniter.

answered Nov 12, 2013 at 14:34

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Nathan Hornby Over a year ago

Edit: $this->db->join($subquery.' mt','mt.group = t.group'); seems to get the subquery working (I got the syntax from here, which must be wrong too stackoverflow.com/questions/14251358/…). However I'm still getting syntax errors unfortunately.

GolezTrol Over a year ago

That might work if the query in $subquery is already contained in parentheses. I don't think it is, but you can change it to $this->db->join("($subquery) mt",'mt.group = t.group');. Only change: double quotes. If you really don't like them, a concatenation like this should work too: $this->db->join('('.$subquery.') mt','mt.group = t.group');

Nathan Hornby Over a year ago

Ah I see - that definitely helped! The resulting query looks very similar to your suggested one now - the only issue I see is that the backticks aren't wrapping the coalesce properly - I'll experiment and see if I can get the output to look better (you can see this backtick issue in my question)

Nathan Hornby Over a year ago

Turns out the backtick issue is a known CI bug - working around that now.

altsyset · Accepted Answer · 2013-11-12 14:08:35Z

1

When you have multiple order by statements u separate them by comma like this

    $this->db->order_by('coalesce(mt.groupcomplete, t.complete), coalesce(mt.mindate, t.date), t.due, t.complete, t.date');

answered Nov 12, 2013 at 14:08

altsyset

3392 silver badges20 bronze badges

3 Comments

Drixson Oseña Over a year ago

It's also the same separated with multiple $this->db->order_by();

Nathan Hornby Over a year ago

As Drixson pointed out that part of the syntax isn't hugely relevant (it works either way), I keep them separated purely for readability. The result is the same though regardless.

altsyset Over a year ago

@DrixsonOseña thanx for the info...I never knew that

Collectives™ on Stack Overflow

MySQL query into Code Igniter Active Directory format?

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related