# How to use sql query?

**URL:** <https://discourse.cakephp.org/t/how-to-use-sql-query/778>\
**Category:** Need Help\
**Created:** [July 22, 2016, 3:06am UTC](https://discourse.cakephp.org/t/how-to-use-sql-query/778 "2016-07-22T03:06:54Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![ken](https://avatars.discourse-cdn.com/v4/letter/k/cc9497/32.png) [@ken](https://discourse.cakephp.org/u/ken)\
**Post date:** [July 22, 2016, 3:06am UTC](https://discourse.cakephp.org/t/how-to-use-sql-query/778/1 "2016-07-22T03:06:55Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Din0saur](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/din0saur/32/323_2.png) [@Din0saur](https://discourse.cakephp.org/u/Din0saur)\
**Post date:** [July 24, 2016, 8:25pm UTC](https://discourse.cakephp.org/t/how-to-use-sql-query/778/2 "2016-07-24T20:25:28Z")

</div>

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

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

---

<div class="post-metadata">

**Author:** ![HypJ3U50N](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/hypj3u50n/32/1803_2.png) [@HypJ3U50N](https://discourse.cakephp.org/u/HypJ3U50N)\
**Post date:** [June 27, 2020, 3:43pm UTC](https://discourse.cakephp.org/t/how-to-use-sql-query/778/3 "2020-06-27T15:43:26Z")

</div>

> [@Din0saur](#):
>
> `ConnectionManager`

This probably doesn’t apply to cakephp 4 anymore?

---

<div class="post-metadata">

**Author:** ![HypJ3U50N](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/hypj3u50n/32/1803_2.png) [@HypJ3U50N](https://discourse.cakephp.org/u/HypJ3U50N)\
**Post date:** [June 27, 2020, 4:29pm UTC](https://discourse.cakephp.org/t/how-to-use-sql-query/778/4 "2020-06-27T16:29:01Z")

</div>

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 :

```auto
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?

---

<div class="post-metadata">

**Author:** ![Zuluru](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/zuluru/32/1230_2.png) [@Zuluru](https://discourse.cakephp.org/u/Zuluru)\
**Post date:** [June 27, 2020, 7:19pm UTC](https://discourse.cakephp.org/t/how-to-use-sql-query/778/5 "2020-06-27T19:19:11Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![HypJ3U50N](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/hypj3u50n/32/1803_2.png) [@HypJ3U50N](https://discourse.cakephp.org/u/HypJ3U50N)\
**Post date:** [June 27, 2020, 7:47pm UTC](https://discourse.cakephp.org/t/how-to-use-sql-query/778/6 "2020-06-27T19:47:11Z")

</div>

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](https://book.cakephp.org/4/en/orm/database-basics.html) instead of querying through the model?

---

<div class="post-metadata">

**Author:** ![Zuluru](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/zuluru/32/1230_2.png) [@Zuluru](https://discourse.cakephp.org/u/Zuluru)\
**Post date:** [June 27, 2020, 8:17pm UTC](https://discourse.cakephp.org/t/how-to-use-sql-query/778/7 "2020-06-27T20:17:56Z")

</div>

Try this:

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

---

<div class="post-metadata">

**Author:** ![HypJ3U50N](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/hypj3u50n/32/1803_2.png) [@HypJ3U50N](https://discourse.cakephp.org/u/HypJ3U50N)\
**Post date:** [June 29, 2020, 9:56am UTC](https://discourse.cakephp.org/t/how-to-use-sql-query/778/8 "2020-06-29T09:56:41Z")

</div>

Meanwhile I found a solution by

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

```

is there a difference in execution / performance etc?

---

<div class="post-metadata">

**Author:** ![Zuluru](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/zuluru/32/1230_2.png) [@Zuluru](https://discourse.cakephp.org/u/Zuluru)\
**Post date:** [June 29, 2020, 2:06pm UTC](https://discourse.cakephp.org/t/how-to-use-sql-query/778/9 "2020-06-29T14:06:27Z")

</div>

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.