CODEWITHSUNDEEP
phpsqlcodeigniter
Zigu
Zigu

Reputation: 1665

Pros and cons of building queries with Active Record

I have this query which I want to run in my PHP application back end. Notionally, sheet is a DB that keeps track of all the sheets we have. Purchases is a DB that keeps track of which users have access to which sheet. The query I want to run is given a user's id, I can get all the sheets that they should have access to. In query form:

select distinct s.wsid, s.name from sheets s, purchases p where 
s.wsid = p.wsid AND p.uid = *value*;

where value is something input by the application

The way I see it there are two ways to go about getting this to work in the back end.

Option 1)

public function getPurchasedSheets($uid){
    if( is_numeric($uid) ){ //check against injections
        $query = "select distinct s.wsid, s.name from sheets s, purchases p 
            where s.wsid = p.wsid AND p.uid = ".$uid.";" ;
        return $this->db->query($query);
    } else {
        return NULL; //or false not quite sure how typing works in PHP
    }
}

Option 2)

public function getPurchasedSheets($uid){
    if( is_numeric($uid) ){ 
        $this->db->select('wsid, name'); 
        $this->db->distinct();
        $this->db->from('purchases');
        //not sure which order the join works in...
        $this->db->join('sheets', 'sheets.wsid = purchases.wsid');
        $this->db->where('uid ='.$uid);
        return $this->db->get();
    } else {
        return NULL; 
    }
}

Source for all the CodeIgniter Active Record commands:

codeigniter.com/user_guide/database/active_record.html

Is there some sort of performance or security difference from doing thing one way or another? Doing it the second way seems so much more confusing to me... This is compounded a little bit because I am not sure how to do referential disambiguation in this style of coding because purchases and sheets both have a uid field but they mean different things (in addition to not being very familiar with the SQL join command in the first place.). Uid (user id) in purchases means that user has purchased that sheet, while Uid in sheets denotes which user owns that sheet.

TL,DR: Basically, I am asking is there a reason I should sink time into looking how to do things the option 2 way?

Upvotes: 2

Views: 659

Answers (2)

Mike
Mike

Reputation: 783

Query binding is the easiest to implement and does the same thing query building does - and doesn't limit you as much as you will find building does.

$query = $this->db->query('SELECT something FROM table WHERE name1=? AND name2=?', array($name1, $name2);
$result = $query->result();

http://codeigniter.com/user_guide/database/queries.html

Upvotes: 1

marramgrass
marramgrass

Reputation: 1411

The main benefits are:

  • Abstraction from the database engine, where the library can take care of database-specific SQL syntax differences for you. Relevant if you ever have/want to change the database you're working with. In theory, the second form should still just work.
  • The 'Active Record' syntax automatically escapes parameters for you.
  • Readability, although that's a matter of taste.

Incidentally, if you're in a PHP 5 environment, the library supports method chaining:

if( is_numeric($uid) ){ 
        return $this->db->select('wsid, name')
                              ->distinct()
                              ->from('purchases')
                              ->join('sheets', 'sheets.wsid = purchases.wsid')
                              ->where('uid ='.$uid)
                              ->get();
                              // nb. didn't check your join() syntax, either :)
    }

Probably off-topic: CodeIgniter's Active Record is more of a query builder than an implementation of Active Record. In case you're wondering. When viewed as a query builder, it makes a bit more sense ;) FWIW, I do love CodeIgniter. I jest from affection.

Upvotes: 7

Related Questions