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

[sobrenome]

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?

[IPCommerceFan]

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();}}

 

[Ryan Ashbrook]

As mentioned, you can chain the method calls:

$select = \IPS\Db::i()->select( ... )
	->join( ... )
	->join( ... )
	->first();

The same applies to forceIndex(), setKeyField(), and setValueField().

[sobrenome]

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();}}

 

[IPCommerceFan]

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;

 

[sobrenome]

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' );}}