I have a query to select data from multiple tables. How do I write its equivalent code in CodeIgniter?
select *
from A
inner join B on (A.ad_no=B.ad_no)
where B.ad_no in (
select ad_no
from A
where $staff!='00:00' and $staff!='0:00'
)
order by B.ctype asc, B.cname asc,B.ad_no asc
I tried a query in CodeIgniter but it's taking longer time to load the result.
You can try the following (i removed the $ sign from staff)
$query = $this->db
->select("*")
->from("A")
->join("B", "A.ad = B.ad_no")
->where("B.ad_no in (select ad_no from A where staff!='00:00' and staff!='0:00')",NULL, false)
->order_by("B.ctype", "ASC")
->order_by("B.cname", "ASC")
->order_by("B.ad_no", "ASC")
->get();
You get a generated output with the following statement
echo $this->db
->select("*")
->from("A")
->join("B", "A.ad = B.ad_no")
->where("B.ad_no in (select ad_no from A where staff!='00:00' and staff!='0:00')",NULL, false)
->order_by("B.ctype", "ASC")
->order_by("B.cname", "ASC")
->order_by("B.ad_no", "ASC")
->get_compiled_select();
get subquery library from https://github.com/NTICompass/CodeIgniter-Subqueries/edit/master/libraries/Subquery.php
try the following code
$this->db->select('*')->from('A');
$this->db->join('b','A.ad_no=B.ad_no','inner');
$sub = $this->subquery->start_subquery('where_in');
$sub->select('ad_no')->from('A')->where("staff!='00:00'")->where("staff!='0:00'");
$this->subquery->end_subquery('B.ad_no', TRUE);
$this->db->order_by('B.ctype','asc');
$this->db->order_by('B.cname','asc');
$this->db->order_by('B.ad_no','asc');
$query=$this->db->get();
If you are finding that your application is taking a long time to provide the result set, perhaps we need more information about your actual desired logic. Using a JOIN then also performing a subquery on each possible row feels expensive.
Maybe you don't actually need every row from A extended by B so long as there is at least one record from A with a matching ad_no and no zeroed time.
Anyhow, I am under the impression that WHERE IN (subquery) is a bad idea. For now, I'll suggest a JOIN subquery.
$sub = $this->db
->distinct()
->select('ad_no')
->where_not_in('staff', ['00:00', '0:00'])
->get_compiled_select('a');
return $this->db
->join('b', 'ad_no')
->join("($sub) x", 'ad_no')
->order_by('b.ctype, b.cname, b.ad_no')
->get('a')
->result();
Renders as:
SELECT *
FROM a
JOIN b USING (ad_no)
JOIN (
SELECT DISTINCT ad_no
FROM a
WHERE staff NOT IN ('00:00', '0:00')
) x USING (ad_no)
ORDER BY b.ctype, b.cname, b.ad_no
where $staff!='00:00' and $staff!='0:00'and why are they with $?