CodeIgniter Active Record Queries W/ Sub Queries

Posted by Mike on Stack Overflow See other posts from Stack Overflow or by Mike
Published on 2012-04-03T17:15:26Z Indexed on 2012/04/03 17:28 UTC
Read the original article Hit count: 180

Filed under:

codeigniter

|

activerecord

Question:

I really am trying to stick to using ActiveRecord and not using straight SQL.. can someone help me convert this to activerecord?

Trying to get the email address and contact name from another table. map_userfields table is a one to many, multiple rows per p.id. one row per p.id per uf.fieldid.

see this screenshot for a reference to the map_userfields table:

enter image description here

Current Non active record query

SELECT
p.id,
(SELECT uf.fieldvalue FROM map_userfields uf WHERE uf.pointid = p.id AND uf.fieldid = 20) As ContactName,
(SELECT uf.fieldvalue FROM map_userfields uf WHERE uf.pointid = p.id AND uf.fieldid = 31) As ContactEmail
FROM
map_points p
WHERE
/** $pointCategory is an array of categories to look for **/
p.type IN($pointCategory)

Note: I am using CodeIgniter 2.1.x, MySQL 5.x, php 5.3

© Stack Overflow or respective owner

Related posts about codeigniter

CodeIgniter's index.php

as seen on Server Fault - Search for 'Server Fault'
Hi, Following CodeIngiter's installation instructions, I extracted CodeIgniter into XAMPP's htdocs folder and placed its index.php at the root. When I navigated to the following url h t t p://127.0.0.1/index.php I got the following error messages: Warning: require_once(C:\xampp\htdocs/system/codeigniter/CodeIgniter… >>> More
How to test controllers with CodeIgniter PART 2?

as seen on Stack Overflow - Search for 'Stack Overflow'
I am having difficulties testing Controllers in Codeigniter: I use Toast but when I invoke my Home Controller class I get an exception that "db" is not defined. Has anybody an idea how to test this 1-1? Thanks class Home_tests extends Toast { function __construct() { parent::__construct(__FILE__); //… >>> More
How can I pad an image with CodeIgniter?

as seen on Stack Overflow - Search for 'Stack Overflow'
My user is uploading an image of any size XX x YY. I want to find the larger dimension and shrink the image to a square of 250x250 with transparent padding being added to make up the difference. Is there any way to accomplish this with CI's Image Lib? >>> More
On learning CodeIgniter

as seen on Stack Overflow - Search for 'Stack Overflow'
I made a PHP-function which returns an array and then I move its value to a variable $a. How can I output it to a web page on CodeIgniter? >>> More
Getting Uploadify to work with Codeigniter ?

as seen on Stack Overflow - Search for 'Stack Overflow'
Has anyone been able to get uploadify to work with codeigniter? Everything I try it will not upload. >>> More

Related posts about activerecord

Rails & ActiveRecord: Appending methods to models that inherit from ActiveRecord::Base

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a standard ActiveRecord model with the following: class MyModel < ActiveRecord::Base custom_method :first_field, :second_field end At the moment, that custom_method is picked up by a module sent to ActiveRecord::Base. The functionality basically works, but of course, it attaches itself… >>> More
Rails ActiveRecord - Best way to perform an include?

as seen on Stack Overflow - Search for 'Stack Overflow'
I have three models: class Book < ActiveRecord::Base has_many :collections has_many :users, :through => :collections end class User < ActiveRecord::Base has_many :collections has_many :books, :through => :collections end class Collection < ActiveRecord::Base belongs_to… >>> More
Getting types of the attributes in an ActiveRecord object

as seen on Stack Overflow - Search for 'Stack Overflow'
I would like to know if it is possible to get the types (as known by AR - eg in the migration script and database) programmatically (I know the data exists in there somewhere). For example, I can deal with all the attribute names: ar.attribute_names.each { |name| puts name } .attributes just… >>> More
Changing ActiveRecord attribute value in before_save hook

as seen on Stack Overflow - Search for 'Stack Overflow'
I needed to fix the encoding of an ActiveRecord attribute and decided to do it in a before_save hook. And at this point I noticed an unexpected feature. When I wanted to change the value of the attribute, simple using the attribute_name=XY did not work as I expected. Instead of that I needed to use… >>> More
Conditions with whitespaces in activerecord find

as seen on Stack Overflow - Search for 'Stack Overflow'
So i am trying to do this Order.find :all, :conditions => "org = 'test org'" what ends up firing is SELECT * FROM `orders` WHERE (org = 'test org') the whitespace in the argument gets stripped. what am i missing.. im really stumped here. please help! >>> More