Thursday, 22 December 2011

Zend Framework Sql quires

Using Zend_Db – SELECT

Zend_Db and its related classes provide a simple SQL database interface for Zend Framework.
To connect to MySql database, we are using Pdo_Mysql adapter :

$db = Zend_Db::factory('Pdo_Mysql', $dbConnect);




You can define $dbConnect directly
 $dbConnect= array('dbname'=>'your_db',
                        'host'=>'localhost',
                        'username'=>'your_user',
                        'password'=>'your_pwd');

OR
First define 'dbconfig' in protected function _initRegistry() of bootstrap.php if your Application.ini file looks like below:
resources.db.params.dbname = "your_db"
resources.db.params.host = "localhost"
resources.db.params.username = "your_user"
resources.db.params.password = "your_pwd"

Then
protected function _initRegistry(){
Zend_Registry::set('dbconfig', $config->resources->db->toArray());
}




Any time you can check your query by print your $select variable by:
echo $select->__toString();

General WayZend Way

SELECT * FROM `products`


$select = $db->select()
             ->from('products');


SELECT p.* FROM `products` AS p


$select = $db->select()
        ->from( array('p' => 'products'));


SELECT * FROM "myschema"."products"


$select = $db->select()
             ->from('myschema.products');
             OR
$select = $db->select()
             ->from('products', '*', 'myschema');


SELECT p."product_id", p."product_name" 
FROM "products" AS p


$select = $db->select()
     ->from(array('p' => 'products'),
          array('p.product_id', 'p.product_name'));


SELECT p."product_id" AS prodno, 
  p."product_name" AS prodname
 FROM "products" AS p


$select = $db->select()
       ->from(array('p' => 'products'),
             array('prodno' => 'product_id',
              'prodname'=>'product_name'));


SELECT p."product_id", LOWER(product_name)
 FROM "products" AS p


$select = $db->select()
        ->from(array('p' => 'products'),
      array('product_id', 'LOWER(product_name)'));


SELECT p."product_id", (p.cost * 1.08) 
 AS cost_plus_tax
 FROM "products" AS p


$select = $db->select()
        ->from(array('p' => 'products'),
               array('product_id',
               'cost_plus_tax' => '(p.cost * 1.08)'));
               OR
$select = $db->select()
     ->from(array('p' => 'products'),
            array('product_id',
       'cost_plus_tax' =>new Zend_Db_Expr('p.cost * 1.08')));


'SELECT p."from" + 10 AS origin
  FROM "products" AS p'

Using SQL keywords as column name

$select = $db->select()
      ->from(array('p' => 'products'),
           array('origin' =>
             '(p.' . $db->quoteIdentifier('from'). ' + 10)'));


SELECT p."product_id", p."product_name"
  FROM "products" AS p

Using columns() method

$select = $db->select()
       ->from(array('p' => 'products'), 'p.product_id')
       ->columns('p.product_name');


SELECT p."product_id", p."product_name", l.*
  FROM "products" AS p JOIN "line_items" AS l
        ON p.product_id = l.product_id


$select = $db->select()
     ->from(array('p' => 'products'),
         array('product_id', 'product_name'))
    ->join(array('l' => 'line_items'),
         'p.product_id = l.product_id');


SELECT p."product_id", p."product_name",
 l."itemname", l."qty"
  FROM "products" AS p JOIN "line_items" AS l
        ON p.product_id = l.product_id


$select = $db->select()
     ->from(array('p' => 'products'),
         array('product_id', 'product_name'))
    ->join(array('l' => 'line_items'),
         'p.product_id = l.product_id', array('itemname','qty');


SELECT *   FROM "table1" JOIN "table2"
   ON "table1".column1 = "table2".column1
   WHERE column2 = 'foo'


$select = $db->select()
        ->from('table1')
        ->joinUsing('table2', 'column1')
        ->where('column2 = ?', 'foo');


SELECT `a`.`id`, `a`.`name`, `b`.`order_id`
FROM `users` AS `a` 
INNER JOIN `orders` AS `b` ON a.id = b.user_id
WHERE a.id = '{$userId}'


$select = $db->select()
 ->from(array('a'=>'users'), array('a.id', 'a.name'))
    ->join(array('b'=>'orders'), 'a.id = b.user_id', array('b.order_id'))
 ->where('a.id = ?', $userId)


SELECT product_id, product_name, price
 FROM "products"  WHERE price > 100.00


$select = $db->select()
       ->from('products',
           array('product_id', 'product_name', 'price'))
       ->where('price > 100.00');


SELECT product_id, product_name, price
   FROM "products"
   WHERE (product_id IN (1, 2, 3))


$productIds = array(1, 2, 3);
$select = $db->select()->from('products',
          array('product_id', 'product_name', 'price'))
     ->where('product_id IN (?)', $productIds);


SELECT product_id, product_name, price
   FROM "products"
   WHERE (price > 100.00) AND (price < 500.00)


$select = $db->select()->from('products',
           array('product_id', 'product_name', 'price'))
       ->where('price > ?', 100.00)
       ->where('price < ?', 500.00);


SELECT product_id, product_name, price
   FROM "products"
   WHERE (price < 100.00) OR (price > 500.00)


$select = $db->select()->from('products',
           array('product_id', 'product_name', 'price'))
       ->where('price < ?', 100.00)        ->orWhere('price > ?', 500.00);


SELECT p."product_id", 
   COUNT(*) AS line_items_per_product
   FROM "products" AS p JOIN "line_items" AS l
        ON p.product_id = l.product_id
   GROUP BY p.product_id


$select = $db->select()
    ->from(array('p' => 'products'),array('product_id'))
    ->join(array('l'=>'line_items'),'p.product_id = l.product_id',
        array('line_items_per_product' => 'COUNT(*)'))
    ->group('p.product_id');


SELECT p."product_id", 
  COUNT(*) AS line_items_per_product
   FROM "products" AS p JOIN "line_items" AS l
        ON p.product_id = l.product_id
   GROUP BY p.product_id 
   HAVING line_items_per_product > 10


$select = $db->select()
    ->from(array('p' => 'products'),array('product_id'))
    ->join(array('l'=>'line_items'),'p.product_id = l.product_id',
        array('line_items_per_product' => 'COUNT(*)'))
    ->group('p.product_id')
    ->having('line_items_per_product > 10');


SELECT p."product_id", 
 COUNT(*) AS line_items_per_product
  FROM "products" AS p JOIN "line_items" AS l
    ON p.product_id = l.product_id
   GROUP BY p.product_id
   ORDER BY "line_items_per_product" DESC,
    "product_id"


$select = $db->select()
    ->from(array('p' => 'products'),array('product_id'))
    ->join(array('l'=>'line_items'),'p.product_id = l.product_id',
        array('line_items_per_product' => 'COUNT(*)'))
    ->group('p.product_id')
    ->order(array('line_items_per_product DESC','product_id'));


SELECT p."product_id", p."product_name"
   FROM "products" AS p
   LIMIT 10, 20 OR LIMIT 20 OFFSET 10


$select = $db->select()
        ->from(array('p' => 'products'),
                    array('product_id', 'product_name'))
        ->limit(20, 10);

Using limit for Paging
SELECT p."product_id", p."product_name"
   FROM "products" AS p
   LIMIT 10, 20


$select = $db->select()
        ->from(array('p' => 'products'),
                    array('product_id', 'product_name'))
        ->limitPage(2, 10);


SELECT DISTINCT p."product_name"
  FROM "products" AS p


$select = $db->select()
         ->distinct()
         ->from(array('p' => 'products'), 'product_name');


SELECT review.* from 
(select rating.user_id as usersid,
sum(rating.rating_value) as rat_val 
from review 
inner join rating 
on rating.user_id = review.user_id 
where rating.item_id = '1692' 
and review.item_id = '1692' 
and rating.`rating_type_id` = '21' 
group by rating.user_id)
 as t  , `review` 
WHERE review.user_id in (t.usersid)
order by rat_val


 $select1 = $table->select()
        ->setIntegrityCheck(false)
 ->from(array('r' => 'review'),
        array('usersid' =>'rt.user_id', 'rat_val' => new Zend_Db_Expr('sum(rt.rating_value)')))
 ->join(array('rt' => 'rating'),'r.user_id = rt.user_id')
 ->where($whereOne)
 ->group('rt.user_id');
 $select = $table->select()
        ->setIntegrityCheck(false)
 ->from(array('t' => $select1))
 ->joinCross(array('rf' => 'review'))
 ->where('rf.user_id IN (t.usersid)')
 ->order($order);





If you using $table = $this->getDbTable(); as dedicated module as suggested in ZF Quick Start GuestBook Example. In this case you must use setIntegrityCheck(false) when you going to perform JOIN
$table = $this->getDbTable();
$select = $table->select()
        ->setIntegrityCheck(false)
        ->from(array('a'=>'agent'))
->join(array('c'=>'customer'), 'a.agent_id = c.id', array('c.first_name','c.last_name'))
        ->where('a.agent_id = ?', $id)
        ->where('a.status = ?', 1);


Ref: http://framework.zend.com/manual/en/zend.db.select.html

13 comments :

  1. Like the new layout. I enjoyed the content. Bless you for this fantastic entry.

    ReplyDelete
  2. Great blog here! Also your website loads up very fast! What web host are you using? Can I get your affiliate link to your host? I wish my web site loaded up as quickly as yours lol

    ReplyDelete
  3. Amazing information and facts! I have been previously looking for something like this for a long time now. Cheers!

    ReplyDelete
  4. Thanks for appreciations, On your request we have added new widget in right sidebar as Resources. Here you can click on the sign-up to see my hosting provider and their details. However, we can say that there are also other points which helps to load page faster.

    ReplyDelete
  5. Hi there, I check your new stuff on a regular basis. Your writing style is witty, keep it up!

    ReplyDelete
  6. Thanks for the marvelous posting! I definitely enjoyed reading it, you happen to be a great author. I will make sure to bookmark your blog and will come back in the foreseeable future. I want to encourage yourself to continue your great writing, have a nice day!

    ReplyDelete
  7. I'm often to running a blog and i really appreciate your content. The article has really peaks my interest. I'm going to bookmark your site and keep checking for brand new information.

    ReplyDelete
  8. I cannot agree with you more! Fantastic blog post, have a very good day, Take care.

    ReplyDelete
  9. bib , http://www.4youcoupons.com/cosmetics/order-phen375/ , bfc . phen375 paypal ecf , lmb , phen375 order , dbe . phen375 in south africa mcm, dic , phen375 user reviews , hmc . phen375 login bbg

    ReplyDelete
  10. cfh , http://www.4youcoupons.com/cosmetics/lose-belly-fat-naturally/ , ddb . phen375 template mah , hmk , phen375 money back guarantee , bjd . phen375 trial beg, bhj , phen375 message boards , ggg . phen375 high blood pressure cbb

    ReplyDelete
  11. Very good blog post. I unquestionably love this site. Stick with it!

    ReplyDelete
  12. Hi there, just wanted to mention, I liked this article. It was helpful. Keep on posting!

    ReplyDelete