Eliminating the UNION statement from the query

[eluser]RobertB.[/eluser]
Hello guys,

I'm trying to find another way to execute this code to not use UNION because it can not be used with active records.

Code:

(SELECT s.state,  SUM(b.is_active) AS active
FROM dr_states AS s
LEFT JOIN dr_businesses AS b ON s.id = b.state_id
LEFT JOIN dr_subcat2bus AS sb ON b.id = sb.bus_id
LEFT JOIN dr_subcats AS sc ON sb.subcat_id = sc.id
WHERE sb.subcat_id = 2)
UNION
(SELECT s.state, b.is_active
FROM dr_states AS s
LEFT JOIN dr_businesses AS b ON s.id = b.state_id
LEFT JOIN dr_subcat2bus AS sb ON b.id = sb.bus_id
LEFT JOIN dr_subcats AS sc ON sb.subcat_id = sc.id
WHERE s.country_id = 1
GROUP BY s.id)

This shows all the states with the number of active business in that subcat and also shows the empty states

I was trying to use it like this but does not work

Code:

$this->db->select('s.id, s.state, SUM(b.is_active) AS active');
$this->db->from('states AS s');
$this->db->join('businesses AS b','s.id = b.state_id', 'left');
$this->db->join('subcat2bus AS sb', 'b.id = sb.bus_id', 'left');
$this->db->join('subcats AS sc', 'sb.subcat_id = sc.id', 'left');
$this->db->where('b.subcat_id', $subcat_id);
$this->db->query('UNION');
$this->db->select('s.id, s.state, b.is_active');
$this->db->from('states AS s');
$this->db->join('businesses AS b','s.id = b.state_id', 'left');
$this->db->join('subcat2bus AS sb', 'b.id = sb.bus_id', 'left');
$this->db->join('subcats AS sc', 'sb.subcat_id = sc.id', 'left');
$this->db->where('s.country_id', 1);

[eluser]kgill[/eluser]
First, you can't mix AR and query like that, you either use one or the other. Secondly, AR starts to break down when it comes to complex queries so either don't use it and just stick with straight SQL or execute the two parts of the query separately in AR and deal with merging the data in your code.

I'm confused about your query though, what's the purpose of the 2nd part of the union? As it's written aren't you going to get more than just the empty states?

[eluser]RobertB.[/eluser]
I wanted to use AR to keep the consistency, that is why I was trying to eliminate the UNION part.
The top part only returns the states that has a listing with that subcategory and the bottom part return all the remaining states

[eluser]RobertB.[/eluser]
SOLVED
Achieving same thing with this query(AR)

Code:

$this->db->select('s.id, s.state, SUM(b.is_active) AS active');
$this->db->from('states s');
$this->db->join('businesses b', 's.id = b.state_id', 'left');
$this->db->join('subcat2bus sb', 'b.id = sb.bus_id', 'left');
$this->db->where('sb.subcat_id', $subcat_id);
$this->db->where('s.country_id', 1);
$this->db->or_where('s.country_id', 1);
$this->db->where('b.state_id', NULL);
$this->db->group_by('s.id');
$this->db->order_by('s.state', "asc");