Need help building complex query in CodeIgniter ActiveRecord

Hi experts,
I need to reproduce this query in CodeIgniter, using ActiveRecord.  I've got very close but CI is putting backquotes where they are not wanted (the yellow-highlighted ones).
Here's the query and CI's interpretation of it...
query as interpreted by CIand here's the code I used to get it.  Part of this stems from CI's lack of support for UNION.
public function get_squad ($fixture_id, $side)
	{	
		$team_clause = 'X';
		if($side=='1') {$team_clause = ' p_1sts ';}
		if($side=='2') {$team_clause = ' p_2nds ';}
		if($side=='3') {$team_clause = ' p_3rds ';}
		if($side=='4') {$team_clause = ' p_4ths ';}
		if($side=='S') {$team_clause = ' p_sundays ';}
		if($side=='T') {$team_clause = ' p_sundays_res ';}
		if($side=='V') {$team_clause = ' p_young_vets ';}
		if($side=='W') {$team_clause = ' p_senior_vets ';}
		
		$this->db->select('first_name, surname, id AS "player_id", "Y" AS "player_picked", fp_keeper AS "keeper", fp_owes_subs AS "subs", fp_if_fit AS "fit", fp_if_available AS "available"');
		$this->db->from('fixtures_players');
		$this->db->join('players_members', 'fp_player_id = id', 'INNER');
		$this->db->where('fp_fixture_id', $fixture_id);
		$this->db->group_by(array('first_name', 'surname'));
		$first_half_inner_union = $this->db->get_compiled_select(); 		
		
		$this->db->select('first_name, surname, id AS "player_id", "N" AS "player_picked", "" AS "keeper", "" AS "subs", "" AS "fit", "" AS "available"');
		$this->db->from('players_members');
		$this->db->where($team_clause, 1);
		$this->db->group_by(array('first_name', 'surname'));
		$second_half_inner_union = $this->db->get_compiled_select(); 
		
		$from_query = '('.$first_half_inner_union.' UNION '.$second_half_inner_union . ') AS player_subquery ';		
		
		$this->db->select_max('first_name');
		$this->db->select_max('surname');
		$this->db->select_max('player_id');
		$this->db->select_max('player_picked');
		$this->db->select_max('keeper');
		$this->db->select_max('subs');
		$this->db->select_max('fit');
		$this->db->select_max('available');	
		$this->db->from($from_query);
		$this->db->group_by(array('surname', 'first_name'));
		$this->db->order_by('surname', 'ASC');
		$this->db->order_by('first_name', 'ASC');
		
		$query = $this->db->get();

		if ( $query->num_rows() > 0 )
		{
			$result_array = $query->result_array();
			return $result_array;
		}	
		else
		{
			return false; // ERROR.
		}		
	}

Open in new window


Yes I could abandon ActiveRecord and just db->query the whole thing but I'd rather not.

As always, any help appreciated.

 Col
LVL 3
colinspursAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Pity that query is an image. Can you provide this part of the query as text please, it can work without a union i believe.  
query-rewrite.png
0
colinspursAuthor Commented:
Thanks for responding.

Sorry for the image.

You are probably right.  Here's the whole query.  Basically, the query gives me a list of players who are in a team squad (eg. p_1sts).  I present this to the user as a form to get those selected for this particular game, and also allow free text entry of names. They can tick their names (in which case the virtual field player_picked is Y) or they can enter a different name which I give player-picked = 'Y' and get their details from the players' members table.   The UNION is aimed at getting both those simply ticked off a form and those whose names were entered manually, and to avoid getting a player twice.

SELECT max(first_name) AS first_name, max(surname) AS surname, max(player_id) AS player_id, 
			max(player_picked) AS player_picked, max(keeper) AS keeper, max(subs) AS subs, max(fit) AS fit, 
			max(available) AS available 
			FROM 
			(SELECT P.first_name AS first_name, P.surname AS surname, P.id AS player_id, 'Y' AS player_picked, 
			FP.fp_keeper AS keeper, FP.fp_owes_subs AS subs, FP.fp_if_fit AS fit, FP.fp_if_available AS available
			FROM fixtures_players FP INNER JOIN players_members P ON FP.fp_player_id = P.id 
			WHERE FP.fp_fixture_id = '6881'            
			UNION 
			SELECT P.first_name AS first_name, P.surname AS surname, P.id AS player_id, 
			'N' AS player_picked, '' AS keeper, '' AS subs, '' AS fit, '' AS available 
			FROM players_members P WHERE P.p_1sts = 1
			) AS players_list 
GROUP BY surname, first_name 
ORDER BY surname, first_name;

Open in new window


And table structures
--Table: fixtures_players

--DROP TABLE IF EXISTS fixtures_players;

CREATE TABLE fixtures_players (
  fp_fixture_id          int NOT NULL,
  fp_player_id           int NOT NULL,
  fp_keeper              tinyint NOT NULL DEFAULT 0,
  fp_owes_subs           tinyint NOT NULL DEFAULT 0,
  fp_if_fit              tinyint NOT NULL DEFAULT 0,
  fp_if_available        tinyint NOT NULL DEFAULT 0,
  fp_comment             varchar(30),
  fp_substitute          tinyint(1) NOT NULL DEFAULT 0,
  fp_sub_used            tinyint(1) NOT NULL DEFAULT 0,
  fp_goals               tinyint(1) DEFAULT 0,
  fp_penalties           tinyint(1) DEFAULT 0,
  fp_own_goals_for_oppo  tinyint(1) DEFAULT 0,
  fp_yellow_card         tinyint(1),
  fp_red_card            tinyint(1),
  /* Keys */
  PRIMARY KEY (fp_fixture_id, fp_player_id)
) ENGINE = MyISAM
  COMMENT = 'Fixtures and players link table';

CREATE INDEX fp_fix_ind
  ON fixtures_players
  (fp_fixture_id);

CREATE INDEX fp_pl_ind
  ON fixtures_players
  (fp_player_id);

Open in new window


--Table: players_members

--DROP TABLE IF EXISTS players_members;

CREATE TABLE players_members (
  id                int AUTO_INCREMENT NOT NULL,
  first_name        varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  surname           varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  tel_mobile        varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci,
  email             varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci,
  p_1sts            tinyint(1) NOT NULL DEFAULT 0,
  p_2nds            tinyint(1) NOT NULL DEFAULT 0,
  p_3rds            tinyint(1) NOT NULL DEFAULT 0,
  p_4ths            tinyint(1) NOT NULL DEFAULT 0,
  p_sundays         tinyint(1) NOT NULL DEFAULT 0,
  p_sundays_res     tinyint(1) NOT NULL DEFAULT 0,
  p_young_vets      tinyint(1) NOT NULL DEFAULT 0,
  p_senior_vets     tinyint(1) NOT NULL DEFAULT 0,
  p_youths          tinyint(1) NOT NULL DEFAULT 0,
  p_academy         tinyint(1) NOT NULL DEFAULT 0,
  keeper            tinyint(1) NOT NULL DEFAULT 0,
  manager           tinyint(1) NOT NULL DEFAULT 0,
  referee           tinyint(1) NOT NULL DEFAULT 0,
  committee_member  tinyint(1) NOT NULL DEFAULT 0,
  kcl_reg           varchar(12),
  sla_reg           varchar(12),
  bl_reg            varchar(12),
  obdsfl_reg        varchar(12),
  date_of_birth     date,
  address_line_1    varchar(25),
  address_line_2    varchar(25),
  town              varchar(25),
  county            varchar(25),
  postcode          varchar(8),
  /* Keys */
  PRIMARY KEY (id)
) ENGINE = MyISAM;

Open in new window

0
colinspursAuthor Commented:
After all that it worked when I removed the erroneous GROUP BYs from the inner queries.

I still believe the query could be made easier but I can't see any improvements myself.
1
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

PortletPaulfreelancerCommented:
I am guessing a bit here but if my assumptions about the data model are correct then I think a left join on fixtures would simplify the query a lot and not repeat any rows so as to avoid the grouping (as long as you are choosing only 1 fixture by the fixture_id).

Please try this alternative query:
SELECT
      p.first_name AS first_name
    , p.surname    AS surname
    , p.id         AS player_id
    , case when p.id IS NOT NULL then 'Y' else 'N'  end AS player_picked
    , fp.fp_keeper       AS keeper
    , fp.fp_owes_subs    AS subs
    , fp.fp_if_fit       AS fit
    , fp.fp_if_available AS available
FROM players_members p
LEFT JOIN fixtures_players fp ON p.id = fp.fp_player_id AND fp.fp_fixture_id = '6881'
WHERE p.p_1sts = 1 or p.id IS NOT NULL
ORDER BY
      surname
    , first_name
;

Open in new window

0
colinspursAuthor Commented:
Great!  I flipped it around to test for fp.fp_player_id being null and it works.  Will check it more thoroughly tomorrow.

Thanks.

SELECT
      p.first_name AS first_name
    , p.surname    AS surname
    , p.id         AS player_id
    , case when fp.fp_player_id IS NOT NULL then 'Y' else 'N'  end AS player_picked
    , fp.fp_keeper       AS keeper
    , fp.fp_owes_subs    AS subs
    , fp.fp_if_fit       AS fit
    , fp.fp_if_available AS available
FROM players_members p
LEFT JOIN fixtures_players fp ON p.id = fp.fp_player_id AND fp.fp_fixture_id = '6798'
WHERE p.p_sundays_res = 1 or fp.fp_player_id IS NOT NULL
ORDER BY
      surname
    , first_name

Open in new window

0
PortletPaulfreelancerCommented:
oops, that was my intention, but you got the idea.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.