CodeIgniter Forums
Active record - incorrect compiled query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Libraries & Helpers (https://forum.codeigniter.com/forum-22.html)
+--- Thread: Active record - incorrect compiled query (/thread-40523.html)



Active record - incorrect compiled query - El Forum - 04-11-2011

[eluser]airween[/eluser]
Hello there,

here is my parts of query:

Code:
$this->db->select("SQL_CALC_FOUND_ROWS pw.partnerworkerid AS pwid,
      CONCAT(pw.firstname, \" \", pw.lastname) AS wname,
      pw.arrangement AS arrangement,
      p.compname AS compname,
      pw.phone1 AS phone1,
      pw.email AS email,
      a.name_short AS agencyname", FALSE);
$this->db->from("partner_worker AS pw");
$this->db->join("partnerco AS p", "p.partnercoid = pw.partnercoid");
$this->db->join("worker_staff AS ws", "ws.partnerworkerid = pw.partnerworkerid");
$this->db->join("agency_staff AS ast", "ws.staffid = ast.staffid");
$this->db->join("agency AS a", "a.agencyid = ast.agencyid");
$this->db->or_like("CONCAT(pw.firstname, \" \", pw.lastname)", $options['searchText']);
$this->db->or_like("p.compname", $options['searchText']);
$this->db->or_where_in("pw.partnerworkerid", $_SESSION['CI_session']['searchresult']);

(I hope this the right relevant code, there are several statements...)

This has compile this query:
Code:
SELECT  pw.partnerworkerid AS pwid, CONCAT(pw.firstname, " ", pw.lastname) AS wname, pw.arrangement AS arrangement, p.compname AS compname, pw.phone1 AS phone1, pw.email AS email, a.name_short AS agencyname
FROM (partner_worker AS pw)
JOIN partnerco AS p ON p.partnercoid = pw.partnercoid
JOIN worker_staff AS ws ON ws.partnerworkerid = pw.partnerworkerid
JOIN agency_staff AS ast ON ws.staffid = ast.staffid
JOIN agency AS a ON a.agencyid = ast.agencyid
WHERE pw.partnerworkerid IN ('10')
AND  CONCAT(pw.firstname, " ", pw.lastname)  LIKE '%3m%'
OR  p.compname  LIKE '%3m%'

but the expected query should be:

Code:
SELECT  pw.partnerworkerid AS pwid, CONCAT(pw.firstname, " ", pw.lastname) AS wname, pw.arrangement AS arrangement, p.compname AS compname, pw.phone1 AS phone1, pw.email AS email, a.name_short AS agencyname
FROM (partner_worker AS pw)
JOIN partnerco AS p ON p.partnercoid = pw.partnercoid
JOIN worker_staff AS ws ON ws.partnerworkerid = pw.partnerworkerid
JOIN agency_staff AS ast ON ws.staffid = ast.staffid
JOIN agency AS a ON a.agencyid = ast.agencyid
WHERE pw.partnerworkerid IN ('10')
OR  CONCAT(pw.firstname, " ", pw.lastname)  LIKE '%3m%'
OR  p.compname  LIKE '%3m%'

so, the different is after first clause should be "OR", instead of "AND".

How can I do that?


Thanks:

a.


Active record - incorrect compiled query - El Forum - 04-11-2011

[eluser]osci[/eluser]
read this post


Active record - incorrect compiled query - El Forum - 04-11-2011

[eluser]airween[/eluser]
Thanks, I'll check it out.

cheers:

a.