Writing Query in Active Record

Writing Query in Active Record - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Writing Query in Active Record (/showthread.php?tid=56704)

Writing Query in Active Record - El Forum - 01-12-2013

[eluser]RMinor[/eluser]
How would I write the following query in CodeIgniter's active record class?

Code:
SELECT event . * , GROUP_CONCAT(CONCAT( base_url() .'media/photos/thumbnail/', photo_thumbnail )

SEPARATOR '|' ) AS thumbnails

FROM event

LEFT JOIN photo ON event_id = photo_event

WHERE event_user = ?

GROUP BY event.event_id

Writing Query in Active Record - El Forum - 01-12-2013

[eluser]PhilTem[/eluser]
Did you have a look at the AR documentation? If not, go for this first, then we can help with solving problems Wink

Writing Query in Active Record - El Forum - 01-12-2013

[eluser]RMinor[/eluser]
Yeah I looked it over. I used it to make every query active record, but couldn't figure out how to make this one work.

Writing Query in Active Record - El Forum - 01-12-2013

[eluser]RMinor[/eluser]
Here is what I have so far...

Code:
$this->db->select('event.*');

$this->db->select('GROUP_CONCAT(CONCAT(' . base_url() . '"media/photos/thumbnail/", photo_thumbnail) SEPARATOR "|" ) AS thumbnails');

$this->db->from('event');

$this->db->join('photo', 'event_id = photo_event', 'left');

$this->db->where('event_user', $user_id);

$this->db->group_by('event.event_id');

$query = $this->db->get();

if ($query->num_rows() > 0) {

    return $query->result_array();

}

return FALSE;

...and this is my error.

Code:
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '://weddingconnect.minorcorpllc.com/"media/photos/thumbnail/", `photo_thumbnail)`' at line 1

SELECT `event`.*, GROUP_CONCAT(CONCAT(http://weddingconnect.minorcorpllc.com/"media/photos/thumbnail/", `photo_thumbnail)` SEPARATOR "|" ) AS thumbnails FROM (`event`) LEFT JOIN `photo` ON `event_id` = `photo_event` WHERE `event_user` = '6' GROUP BY `event`.`event_id`

Writing Query in Active Record - El Forum - 01-12-2013

[eluser]RMinor[/eluser]
Okay I figured it out. Here is the finished query for those interested.

Code:
$this->db->select('event.*');

  $this->db->select('GROUP_CONCAT(CONCAT(\'' . base_url() . 'media/photos/thumbnail/\', photo_thumbnail ) SEPARATOR \'|\' ) AS thumbnails');

  $this->db->from('event');

  $this->db->join('photo', 'event_id = photo_event', 'left');

  $this->db->where('event_user', $user_id);

  $this->db->group_by('event.event_id');

  $query = $this->db->get();

  if ($query->num_rows() > 0) {

   return $query->result_array();

  }

  return FALSE;

Writing Query in Active Record - El Forum - 01-12-2013

[eluser]CroNiX[/eluser]
You should also look at the 2nd parameter of db: Confused

elect() for more complex queries.

Writing Query in Active Record - El Forum - 01-12-2013

[eluser]RMinor[/eluser]
Okay I will keep that in mind when writing complex ones. Also, do I need to add FALSE as a second parameter to the second select.

Writing Query in Active Record - El Forum - 01-12-2013

[eluser]CroNiX[/eluser]
Not if it's working...

Here is a similar select I have to show...

Code:
$db->select("CONCAT('http://listings.', user_agent_profile.website_url, 'somedomain.com/i/', user_agent_profile.website_idx_cid, '/') as idx_url", FALSE)

Notice how much cleaner it is (it's also all wrapped in double quotes while quoted text within the query is using single quotes - such as in the CONCAT. You're using single quotes for everything which makes you have to escape them first for the ones that are actually supposed to be quotes in your query)