How to use sql query?

Need Help
1

hi,
Q1. how can I use sql query as show below? is there any configure needed in cakephp or query ? please give some guideline.
$aql = $this->Models->query("select abc from TSpec where SC= ? ,‘169/02’ ");

I feel there is limitation using find / field as below, so decide to use sql query, anybody can give some guideline,
how to use it ?

below sample was work as normal, but the above doest;t show any data ?
$aql = $this->Models->field(‘abc’, array(‘sc’ => ‘169/02’ ));

please give some guide.
Thankyou

2

I’m not sure if my reply really address your question, so be patient if not! :slight_smile:

use Cake\Datasource\ConnectionManager;

and then
$conn = ConnectionManager::get(‘default’);

    $results = $conn->newQuery()
                  ->select('abc')
                  ->from('TSpec')
                  ->where(['SC =' => '169/02'])
                  ->execute()
                  ->fetchAll('assoc');

or if you prefer:

   $query = "select abc from TSpec where SC='169/02'";
   $results = $this->conn->execute($query)->fetchAll('assoc');
3

This probably doesn’t apply to cakephp 4 anymore?

4

I have a new issue.

If I try to reproduce the basic query as in the documentation:
$result=$this->Gpxfiles->query("SELECT 'name' FROM 'gpxfiles' LIMIT 2");
I do not receive an array, but the query object itself. Moreover, it does not contain the query I requested, but a general select * FROM :

object(Cake\ORM\Query) {
	'(help)' => 'This is a Query object, to get the results execute or iterate it.',
	'sql' => 'SELECT Gpxfiles.id AS Gpxfiles__id, Gpxfiles.linestring AS Gpxfiles__linestring, ... FROM gpxfiles Gpxfiles',
'params' => [],
	'defaultTypes' =>...

Any idea what might cause this?

5

No idea why the SQL wouldn’t match what you’re looking for. You don’t get an array, because you haven’t asked it for one, you’re presumably just outputting the actual query object.

Why not use $this->Gpxfiles->find()->select('name')->limit(2)? I’ve built some very complex systems, and have not once had to resort to using the query function.

6

Because eventually I want to use
$result_gpx=$this->Gpxfiles->query("SELECT ST_AsGeoJSON(linestring) FROM gpxfiles WHERE id=gpx_id_string");

for which I will need a custom query. The reason why I started off with a basic question is to understand it step by step in order to understand the eventual query.

Maybe I have to look into https://book.cakephp.org/4/en/orm/database-basics.html instead of querying through the model?

7

Try this:

$result_gpx = $this->Gpxfiles->find()
    ->select(['GeoJSON' => 'ST_AsGeoJSON(linestring)'])
    ->where(['id' => 'gpx_id_string']);
1 Like
8

Meanwhile I found a solution by

$connection = ConnectionManager::get('default');
$query_lon_lats=$connection->execute(..)->fetchAll('assoc');

is there a difference in execution / performance etc?

9

Unless you’re running this in a loop that executes thousands of times, the performance difference between the two will not be noticeable. Go with the one version that is more long-term maintainable for you.

1 Like