CODEWITHSUNDEEP
codeigniterquery-builder
Aditya B
Aditya B

Reputation: 11

how to write this complex query in codeigniter

SELECT 
    fname,
    lname,
    email,
    phone,
    activities.activitydescription, 
    package.PackageName, 
    reservationyurt.arrvdate 
FROM 
    client
    INNER JOIN activities ON client.activityid = activities.activityid)
    INNER JOIN package ON client.packageid = package.packageid)
    INNER JOIN reservationyurt ON client.packageid = reservationyurt.packageid)
WHERE 
    email = '[email protected]' 
    AND clientid IN (
        SELECT MAX(clientid)
        FROM client GROUP BY email
    )

I want to write this query using the CodeIgniter QueryBuilder. This is what I got so far...

$email = array('email' => $this->input->post('email'));
$this->db->select('fname, lname, email, phone, activities.activitydescription, package.pkgname, reservationyurt.arrvdate');
$this->db->from('client');
$this->db->join('activities', 'client.activityid = activities.activityid');
$this->db->join('package', 'client.packageid = package.packageid');
$this->db->join('reservationyurt', 'client.packageid = reservationyurt.packageid');
$this->db->where('email',$email);
$this->db->where('clientid');

Upvotes: 1

Views: 193

Answers (2)

DFriend
DFriend

Reputation: 8964

Query Builder is a nice tool but you don't always NEED to use it. Unless you have a query that conditionally needs to change the select, where or someother part of a query it is often much easier to use query()

$sql = "SELECT 
    fname,
    lname,
    email,
    phone,
    activities.activitydescription, 
    package.PackageName, 
    reservationyurt.arrvdate 
FROM client
    INNER JOIN activities ON client.activityid = activities.activityid)
    INNER JOIN package ON client.packageid = package.packageid)
    INNER JOIN reservationyurt ON client.packageid = reservationyurt.packageid)
WHERE email = ? AND 
    clientid IN (SELECT MAX(clientid) FROM client GROUP BY email)";

$data = array('[email protected]');
$query = $this->db->query($sql, $data);
$dataset = $query->result();

Basically what the correct set of Query Builder calls would do is create the string above but it will execute hundreds of lines of code to get there.

Note the use of Query Binding in WHERE email = ?.

Upvotes: 0

Pradeep
Pradeep

Reputation: 9707

Hope this will work for you :

use where_in for the last part 

$this->db->select('fname, lname, email, phone, activities.activitydescription, package.pkgname, reservationyurt.arrvdate');
$this->db->from('client');
$this->db->join('activities', 'activities.activityid = client.activityid');
$this->db->join('package', 'package.packageid = client.packageid');
$this->db->join('reservationyurt', 'reservationyurt.packageid = client.packageid');
$this->db->where('client.email',$email);
$this->db->where_in('client.clientid',"SELECT MAX(clientid) FROM client GROUP BY email)");

For more : https://www.codeigniter.com/user_guide/database/query_builder.html#looking-for-specific-data

Upvotes: 1

Related Questions