logo
Ask your Symfony questions! Pay money and get answers fast! (more info)

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.

$25
sfPager is looking to "id" field even though that is not the PK

A friend sends me this question. They are having a problem with pagination on a site running Symfony 1.0. They have created a database view which combines 2 database tables, and then they created a model class to draw from the database view. One of the database tables had a field called "id" but this is not the primary key in the database view (it was a primary key in the original table). This database view was created with a LEFT JOIN so sometimes the field "id" is null in the database view.

On one particular page my friend knows they should get 19 results. However, they only get 15 results. In 4 cases the field "id" has a value of "null". The pager will not get these rows.

Here is the action code:

public function GetItemsFromPhum( $listid, $filter, $sort='instrument_name', $order='A', $active=true, $size, $page, $paginated='true')
{

$size = intval($size);
$page = intval($page);

$filter = trim($filter);
$filter = str_ireplace("%",'', $filter);
$filter = str_ireplace("_",'', $filter);


$c = new Criteria();

$c->add( self::CA_USER_ID, $this->userid );

if ( $listid == 'cabinetview')
$c->add( self::ALWAYS_ELECTRONIC, 'Y');
else
{
if(!empty( $listid ))
$c->add( self::PH_NAME, $listid );
else
$c->add( self::PH_NAME, '' );
}
if ($active)
$c->add( self::DELETED, null, Criteria::ISNULL );
else
$c->add( self::DELETED, 'Y' );

if (! empty($filter))
{

$c1 = $c->getNewCriterion(self::INSTRUMENT_NAME, '%'.$filter.'%', Criteria::LIKE);
$c2 = $c->getNewCriterion(self::NR_INSTRUMENT_NAME, '%'.$filter.'%', Criteria::LIKE);
$c3 = $c->getNewCriterion(self::BRAND_NAME, '%'.$filter.'%', Criteria::LIKE);
$c4 = $c->getNewCriterion(self::NR_BRAND_NAME, '%'.$filter.'%', Criteria::LIKE);
$c5 = $c->getNewCriterion(self::COUNTRY, '%'.$filter.'%', Criteria::LIKE);
$c6 = $c->getNewCriterion(self::NR_COUNTRY, '%'.$filter.'%', Criteria::LIKE);

$c1->addOr($c2)->addOr($c3)->addOr($c4)->addOr($c5)->addOr($c6);
$c->addAnd($c1);
}

if ($order == 'D')
$orderclause = 'addDescendingOrderByColumn';
else
$orderclause = 'addAscendingOrderByColumn';

switch($sort)
{
case 'instrument_name':
$c->{$orderclause}('COALESCE(\' . self::INSTRUMENT_NAME. \', \'. self::NR_INSTRUMENT_NAME .\')' );
break;

case 'brand_name':
$c->addAscendingOrderByColumn('COALESCE(' . self::BRAND_NAME. ', '. self::NR_BRAND_NAME .')');
break;

case 'size':
$c->{$orderclause}('COALESCE(' . self::INSTRUMENT_SIZE. ', '. self::NR_INSTRUMENT_SIZE .')' );
break;

case 'score':
$c->{$orderclause}('COALESCE(' . self::SCORE. ', '. self::NR_SCORE .')' );
break;

case 'price':
$c->{$orderclause}('COALESCE(' . self::US_PRICE. ', '. self::NR_US_PRICE .', '. self::UK_PRICE.', '.self::NR_UK_PRICE .')' );
break;

case 'country':
$c->{$orderclause}('COALESCE(' . self::COUNTRY. ', '. self::NR_COUNTRY .')' );
break;

default:
$c->{$orderclause}('COALESCE(' . self::SORT_BY. ', '. self::NR_SORT_BY .')' );
break;
}

if ($paginated == 'true')
{
$pager = new sfPropelPager('InstrumentLog2', $size);

$pager->setCriteria($c);
$pager->setPage($page);
$pager->setPeerMethod('doSelect');
$pager->init();

$phumitems = $pager;
}
else
$phumitems = self::doSelect($c);

return($phumitems);
}



The pager feels the need to run this query:


SELECT COUNT(instrument_log2.ID) FROM instrument_log2 WHERE instrument_log2.CA_USER_ID=16168 AND instrument_log2.PH_NAME='' AND instrument_log2.DELETED IS NULL



This forum post almost seemed to suggest an answer:


Just pass the query to the init method. I can't be more specific

For example, instead of doing the regular $sfPager->init(), you'd do something like
$rsPager->init("SELECT COUNT(ID) FROM tables");

Obviously this is a query example, you need to build your 'count' query depending on your criteria.


But that doesn't work for us, possibly because we are not doing a raw query.

Any suggestions? We want the pager to return 19 rows, including the 4 rows that have 'null' as a value for the id field.

Here is the way the database view is defined in the schema.yml:

# Here is the query used for the second view
# CREATE VIEW instrument_log2 AS
# select * from instrument_personal_electronic
# left join instrumentlog2 on instrument_personal_electronic.RATINGS_KEY = instrumentlog2.ID
#
instrument_log2:
ph_id: { type: integer }
ca_user_id: { type: integer }
last_updated: { type: TIMESTAMP }
ph_name: { type: varchar(100) }
user_note: { type: varchar(3000) }
visible: { type: varchar(1) }
nr_brand_name: { type: varchar(100) }
nr_instrument_name: { type: varchar(100) }
nr_instrument_size: { type: varchar(30) }
nr_length: { type: varchar(10) }
nr_ring_gauge: { type: varchar(50) }
nr_filler_gauge: { type: varchar(100) }
nr_binder_gauge: { type: varchar(100) }
nr_wrapper_gauge:{ type: varchar(100) }
nr_us_price: { type: varchar(20) }
nr_uk_price: { type: varchar(20) }
nr_country: { type: varchar(50) }
nr_score: { type: integer }
nr_sort_by: { type: varchar(100) }
quan_purchased: { type: integer }
date_purchased: { type: date }
date_last_smoke: { type: date }
price_purchased: { type: integer }
price_current: { type: integer }
quan_remain: { type: integer }
location: { type: varchar(100) }
status_flag: { type: varchar(100) }
ratings_key: { type: integer }
deleted: { type: varchar(1) }
rowcost: { type: integer }
rowcostsource: { type: varchar(30) }
rowvalue: { type: integer }
rowvaluesource: { type: varchar(30)}
rowgainloss: { type: integer }
rowgainlosspct: { type: integer }
always_electronic: { type: varchar(1) }
id: { type: integer}
country: { type: varchar(30) }
score: { type: integer }
brand_name: { type: varchar(70) }
instrument_size: { type: varchar(30) }
instrument_name: { type: varchar(70) }
ring_gauge: { type: varchar(22) }
length: { type: varchar(10) }
filler_gauge: { type: varchar(100) }
binder_gauge: { type: varchar(100) }
wrapper_gauge: { type: varchar(100) }
tasting_date: { type: date }
tasting_note: { type: longvarchar }
uk_price: { type: varchar(2) }
brand_upper: { type: varchar(70) }
sort_by: { type: varchar(50) }
brand_plus_instrument: { type: varchar(100) }
us_price: { type: integer }
old_pwl_key: { type: longvarchar }
issue_date: { type: date }
source: { type: varchar(100) }
made_by: { type: varchar(100) }
production_date: { type: varchar(50) }
countries_sold: { type: varchar(100) }
strength: { type: varchar(100) }
recommendation: { type: varchar(100) }
production: { type: varchar(100) }
rank: { type: varchar(20) }
distributed_by: { type: varchar(100) }
factory: { type: varchar(100) }
purchase_date: { type: date }
release_date: { type: date }
top25_rank: { type: integer }
top25_year: { type: integer }

This question has been answered.

Lawrence Krubner | 06/24/10 at 2:43pm Edit


(3) Responses

See a threaded view of answers?

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

  • avatar
    Last edited:
    06/25/10
    3:20pm
    Arturo Linares says:

    "id" is the default PK in symfony, even if you don't specify one. Maybe if you select another column in the schema as the PK it will work.

    Propel uses ActiveRecord, and AFAIK it requires all objects it pulls from the DB to have an ID.

  • avatar
    Last edited:
    06/25/10
    3:20pm
    Jakub Zalas says:

    As Arturo suggests your problem is caused by the magic happening in propel. Propel takes "id" column as a primary key as it's not given explicitly.

    One option is to tell propel which field is really a primary key in your schema file.

    Another option could be renaming "id" column in your view. The drawback is you cannot use "SELECT * FROM" but you have to specify all the fields in your view's query (at least from the instumentlog2 table):


    CREATE VIEW instrument_log2 AS
    select ipe.*, il2.id as instrumentlog2_id, il2.country, il2.score, il2.brand_name, [...]
    from instrument_personal_electronic ipe
    left join instrumentlog2 il2 on ipe.RATINGS_KEY = il2.ID

  • avatar
    Last edited:
    06/25/10
    7:50am
    Martin Palacio says:

    You both are right guys.

    I work a lot with views in symfony, always by defining just one field named 'id' to act as PK for Propel. In your case, you may rename (or drop) the 'id' column from the view definition, but you will still need a field exclusively named 'id'.

    On the other hand, the view definition seems to be quite simple...did you tried with Propel joins?


    $c->new Criteria();
    $c->add(InstrumentPersonalElectronicPeer::RATINGS_KEY, InstrumentLog2Peer::ID, Criteria::LEFT_JOIN);
    // ...



    maybe within a peer method, passing it to the pager?

This question has expired.





Current status of this question: Completed



Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.