sobrenome Posted November 18, 2020 Share Posted November 18, 2020 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 More sharing options...
IPCommerceFan Posted November 18, 2020 Share Posted November 18, 2020 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();}} sobrenome 1 Link to comment Share on other sites More sharing options...
Ryan Ashbrook Posted November 18, 2020 Share Posted November 18, 2020 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 and IPCommerceFan 2 Link to comment Share on other sites More sharing options...
sobrenome Posted November 19, 2020 Author Share Posted November 19, 2020 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 More sharing options...
IPCommerceFan Posted November 19, 2020 Share Posted November 19, 2020 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 1 Link to comment Share on other sites More sharing options...
sobrenome Posted November 19, 2020 Author Share Posted November 19, 2020 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' );}} IPCommerceFan 1 Link to comment Share on other sites More sharing options...
Recommended Posts