Random Query Zend

Question

I search lot but not getting any help on random records with mysql.I am using random query to get random data from database with not in with some limit. If database contains 100 records and suppose I am giving 30 as limit with random function and some not in id.My problem is that when it hit first time I am giving not in id as empty and it is giving me 30 records randomly.But for second time except last 30 records i.e(it is in not in variable) it should give me another 30 records from 70 records but it is giving me less than 30 i.e(28,29).This because first it applies random function and limit and then filter it with not in.My query is like below:

$subQuery = $this->select()

->from(array('s' => 'shop'), array('s.shop_id','b.shop_name','b.shop_template_id'))
->order('RAND()')
->where('s.shop_id NOT in (?)', $shop_id)
->limit(30);

$query = $this->select() 

->from(array('b' => $subQuery), array('*'))
->join(array('p' => 'product'), 's.shop_id = p.shop_id', array('p.product_price'))
->setIntegrityCheck(false);
$resultRows = $this->fetchAll($query);

Update: I got the problem why it is giving (28,29) records sometime because join query contains product for shop and if some shop is having 0 products it does not get that shop.My question is that irrespective of product how can i get that shop from database.

Answer 1

Your problem is that you join instead of left join. Also there's no need for a sub select. This should work:

$objSelect = $this->select()->setIntegrityCheck(false);
$objSelect->from(
    array('s' => 'shop'),
    array('s.shop_id','b.shop_name','b.shop_template_id')
);
$objSelect->joinLeft(
    array('p' => 'product'),
    's.shop_id = p.shop_id',
    array('p.product_price')
);
$objSelect->order('RAND()');
$objSelect->limit(30);
$objRowSet = $this->fetchAll($objSelect);