Jump to content

Combine ->first() and ->join() on database query


Recommended Posts

I am running this database query:

{{$select = \IPS\Db::i()->select( 'cms_custom_database_12.field_142 as preco, cms_custom_database_12.field_154 as link, cms_custom_database_12.record_updated as atualizado, cms_custom_database_13.field_143 as loja, cms_custom_database_13.record_image_thumb as logo, cms_custom_database_13.field_160 as cupom, cms_custom_database_13.field_161 as cupom_valor, cms_custom_database_13.primary_id_field as loja_ID', 'cms_custom_database_12', array( array( 'field_156=?', $row->primary_id_field ), array( 'field_141=?', 1 ) ), 'cms_custom_database_12.field_142 ASC')->join( 'cms_custom_database_13', 'cms_custom_database_13.primary_id_field=field_155', 'INNER' );}}

I would like to get just the first row, I do not want to loop over all results.

How to use ->first() in this query?

Link to comment
Share on other sites

I hope this isn't stating the obvious, but what happens when you try this?

{{$select = \IPS\Db::i()->select( 'cms_custom_database_12.field_142 as preco, cms_custom_database_12.field_154 as link, cms_custom_database_12.record_updated as atualizado, cms_custom_database_13.field_143 as loja, cms_custom_database_13.record_image_thumb as logo, cms_custom_database_13.field_160 as cupom, cms_custom_database_13.field_161 as cupom_valor, cms_custom_database_13.primary_id_field as loja_ID', 'cms_custom_database_12', array( array( 'field_156=?', $row->primary_id_field ), array( 'field_141=?', 1 ) ), 'cms_custom_database_12.field_142 ASC')->join( 'cms_custom_database_13', 'cms_custom_database_13.primary_id_field=field_155', 'INNER' )->first();}}

 

Link to comment
Share on other sites

It does not work. An error is prompted:

UnderflowException:  (0)
#0 /var/www/html/applications/cms/sources/Theme/Theme.php(610) : eval()'d code(1214): IPS\Db\_Select->first()
#1 /var/www/html/applications/cms/sources/Theme/Theme.php(610) : eval()'d code(697): IPS\Theme\class_cms_database_suplementos_record_listing_4_5->recordRow()
#2 /var/www/html/system/Helpers/Table/Table.php(564): IPS\Theme\class_cms_database_suplementos_record_listing_4_5->categoryTable()
#3 /var/www/html/applications/cms/modules/front/database/category.php(697): IPS\Helpers\Table\_Table->__toString()
#4 /var/www/html/applications/cms/modules/front/database/index.php(48): IPS\cms\modules\front\database\_category->view()
#5 /var/www/html/system/Dispatcher/Controller.php(101): IPS\cms\modules\front\database\_index->manage()
#6 /var/www/html/applications/cms/sources/Databases/Dispatcher.php(327): IPS\Dispatcher\_Controller->execute()
#7 /var/www/html/system/Theme/Theme.php(4481) : eval()'d code(9): IPS\cms\Databases\_Dispatcher->run()
#8 /var/www/html/applications/cms/sources/Pages/Page.php(1261): IPS\Theme\content_pages_55()
#9 /var/www/html/applications/cms/sources/Pages/Page.php(2266): IPS\cms\Pages\_Page->getHtmlContent()
#10 /var/www/html/applications/cms/modules/front/pages/page.php(116): IPS\cms\Pages\_Page->output()
#11 /var/www/html/applications/cms/modules/front/pages/page.php(43): IPS\cms\modules\front\pages\_page->view()
#12 /var/www/html/system/Dispatcher/Controller.php(101): IPS\cms\modules\front\pages\_page->manage()
#13 /var/www/html/applications/cms/modules/front/pages/page.php(33): IPS\Dispatcher\_Controller->execute()
#14 /var/www/html/system/Dispatcher/Dispatcher.php(152): IPS\cms\modules\front\pages\_page->execute()
#15 /var/www/html/index.php(13): IPS\_Dispatcher->run()
#16 {main}

The query:

{{$select = \IPS\Db::i()->select( 'cms_custom_database_12.field_142 as preco, cms_custom_database_12.field_154 as link, cms_custom_database_12.record_updated as atualizado, cms_custom_database_13.field_143 as loja, cms_custom_database_13.record_image_thumb as logo, cms_custom_database_13.field_160 as cupom, cms_custom_database_13.field_161 as cupom_valor, cms_custom_database_13.primary_id_field as loja_ID', 'cms_custom_database_12', array( array( 'field_156=?', $row->primary_id_field ), array( 'field_141=?', 1 ) ), 'cms_custom_database_12.field_142 ASC')->join( 'cms_custom_database_13', 'cms_custom_database_13.primary_id_field=field_155', 'INNER' )->first();}}

 

Link to comment
Share on other sites

Underflow exception would indicate there aren't any results for the query, I've seen.

If it is normal for this query to sometimes not return a result, then you may need to try:

{{ try { 
    $select = \IPS\Db::i()->select( 'cms_custom_database_12.field_142 as preco, cms_custom_database_12.field_154 as link, cms_custom_database_12.record_updated as atualizado, cms_custom_database_13.field_143 as loja, cms_custom_database_13.record_image_thumb as logo, cms_custom_database_13.field_160 as cupom, cms_custom_database_13.field_161 as cupom_valor, cms_custom_database_13.primary_id_field as loja_ID', 'cms_custom_database_12', array( array( 'field_156=?', $row->primary_id_field ), array( 'field_141=?', 1 ) ), 'cms_custom_database_12.field_142 ASC')->join( 'cms_custom_database_13', 'cms_custom_database_13.primary_id_field=field_155', 'INNER' )->first();
}
catch( \UnderflowException $e ) {
    $select = ''; } }}

Do you get any results when you run this query in mysql directly?

SET @field_156 ='yourdata';

SELECT cms_custom_database_12.field_142 as preco, 
cms_custom_database_12.field_154 as link, 
cms_custom_database_12.record_updated as atualizado, 
cms_custom_database_13.field_143 as loja, 
cms_custom_database_13.record_image_thumb as logo, 
cms_custom_database_13.field_160 as cupom, 
cms_custom_database_13.field_161 as cupom_valor, 
cms_custom_database_13.primary_id_field as loja_ID

FROM cms_custom_database_12
INNER JOIN cms_custom_database_13 
ON cms_custom_database_13.primary_id_field = cms_custom_database_12.field_155

WHERE field_156 = @field_156
AND field_141=1
ORDER BY cms_custom_database_12.field_142 ASC
LIMIT 1;

 

Link to comment
Share on other sites

6 hours ago, IPCommerceFan said:

Underflow exception would indicate there aren't any results for the query, I've seen.

There are results. I have changed the query to limit it to one row instead of using ->first();

{{$select = \IPS\Db::i()->select( 'cms_custom_database_12.field_142 as preco, cms_custom_database_12.field_154 as link, cms_custom_database_12.record_updated as atualizado, cms_custom_database_13.field_143 as loja, cms_custom_database_13.record_image_thumb as logo, cms_custom_database_13.field_160 as cupom, cms_custom_database_13.field_161 as cupom_valor, cms_custom_database_13.primary_id_field as loja_ID', 'cms_custom_database_12', array( array( 'field_156=?', $row->primary_id_field ), array( 'field_141=?', 1 ) ), 'cms_custom_database_12.field_142 ASC', 1)->join( 'cms_custom_database_13', 'cms_custom_database_13.primary_id_field=field_155', 'INNER' );}}

 

Link to comment
Share on other sites

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...