0

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).

Community
  • 1
  • 1
Nathan Hornby
  • 1,423
  • 16
  • 32
  • try using `$this->db->last_query()` to see what's wrong with your query – Drixson Oseña Nov 12 '13 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 Nov 12 '13 at 14:15
  • 1
    see this `('task' t)` I think it should be like `('task') t` anyways, try replacing `t` as task – Drixson Oseña Nov 12 '13 at 14:22
  • If my hunch is right I encountered this problem and was related to `$this->db->select('*')` – Drixson Oseña Nov 12 '13 at 14:23
  • The problem is with coalesce method and coz the first argument is not being treated as the second so you might want to try specifying it with `` operators – altsyset Nov 12 '13 at 14:25
  • Hi @altsyset - could you elaborate a little for me please? What should I wrap in back-ticks? I do see that that coalesce(mt.groupcomplete, \`t\`.\`complete)\`, looks wrong, as the ticks aren't wrapping correctly. – Nathan Hornby Nov 12 '13 at 14:29
  • 1
    @altsyset For future reference that backtick issue is a known bug in CI - https://github.com/EllisLab/CodeIgniter/issues/296 Whitespace fixes it. – Nathan Hornby Nov 12 '13 at 15:07
  • @NathanHornby thanks that is informative – altsyset Nov 12 '13 at 16:56

2 Answers2

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');
altsyset
  • 339
  • 2
  • 20
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.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
  • 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 http://stackoverflow.com/questions/14251358/codeigniter-join-select-query). However I'm still getting syntax errors unfortunately. – Nathan Hornby Nov 12 '13 at 14:37
  • 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');` – GolezTrol Nov 12 '13 at 14:51
  • 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 Nov 12 '13 at 14:59
  • Turns out the backtick issue is a known CI bug - working around that now. – Nathan Hornby Nov 12 '13 at 15:06