Load Model Tablename instead of Modelname to ->query()

Need Help
1

Let’s say I want to query in a foreign model XFiles.
I need to run the query like $this->XFile->query()

query() uses the table name in the query as the array key for the returned data, rather than the model name
Retrieving Your Data - 2.x

but I can only load $this->loadModel(‘XFiles’) since my table name is xfiles and not xfile.
At this SO post it seems possible to load the table name:

How should I make this work? both $this->loadModel(‘XFile’) and $this->loadModel(‘Xfile’) clearly give errors as these corresponding tables do not exist.

2

It’s not really clear what your exact problem is here. Possibly you simply need to use the ‘AS’ command to create name aliases in your query?

$query = 'SELECT first_name AS moniker FROM people AS People WHERE 1=1';
$this->Person->query($query);
3

I think the problem lies one level above that, so not in the query but in accessing the model.
In your example, I would get the error: Call to a member function query() on null.
Since I should use $this->Persons->query(),
since I cannot load $this->loadModel(‘Person’). However, the documents state I should use Person (or in my case XyzFile from table xyzfiles, which may be important due to camel casing).

4

the query does work (that means, without error) if I use $this->Persons->query(), but why does the documentation state I should use the singular TableName of the model?

I don’t know if this causes my query to give the wrong results or if it is my query itself. For example, I don’t know if should still use the ->toArray() function after the query or in the correct way, the query will immediately store the results.

Now,
$this->CamelPlurals->query("SELECT GROUP_CONCAT(CONCAT(x,' ',y) SEPARATOR ',') FROM camelplurals WHERE camelplural_id=var_id_string ORDER BY id")->toArray();

(where var_id_string is $var_id_string=strval($currentcontrollerentity->id) )

gives me the complete camelplurals table in an array, if I use debug the browser has issues with that, as you can imagine :sweat_smile:.
UPDATE: if I print the query as a string, I see the query becomes a written-out select * query that stops after FROM table, thus ignoring the WHERE. Hence explaining the complete table output.

5

The name of the Table class referenced as

  • $this->XyzFile
  • $this->XyzFiles
  • $this->People
  • $this->Person

is in your control. There are names you can expect if you follow naming conventions, but you can change them if you have a need.

If you have a recommended lowercase plural table name in your db (eg xyz_files) and you bake the model with Cake’s command line tools you will get the Table class XyzFilesTable and will be able to call $this->XyzFiles->query().

If your db table name is non-standard (eg xyzfile) and you bake, you will get XyzfileTable and will be able to call $this->Xyzfile->query(). But a cascade of side-effects will begin and you will have to account for them. For example, the standard Entity property will now be the same as the table property; $this->Xyzfile.

To understand the conventions:
https://book.cakephp.org/4/en/intro/conventions.html

But every call you make targets a class in your application. You can easily look and see what you should be calling, it is not arbitrary.

If you are using a command like $this->loadModel(‘Person’), you are saying that you have the file src/Model/Table/PersonTable.php. and that its basic structure is along these lines:

<?php
namespace App\Model\Table;

use Cake\ORM\Table;

/**
 * Person Model
 */
class PersonTable extends Table
{
   // your methods here
}
6

OK, then it works as I expected after all, I am just confused by the documentation which does not follow it’s own conventions then.

query() uses the table name in the query as the array key for the returned data, rather than the model name. For example:

$this->Picture->query(“SELECT * FROM pictures LIMIT 2;”);

Since this should be Pictures according to the conventions?

7

Might be a good time to submit a correction to the docs?

8

Yes, if you confirm it is indeed an inconsistency and not another thing I did not understand myself :wink:

9

Yes. That example deviates from standards in the table naming and does not say it is doing that. So it is not especially helpful to a new user that still trying to absorb the conventions.

On the other hand, it is a section of the docs that is dealing with an advanced, and rarely needed feature.

The reason this particular feature (->query($sql)) should be used sparingly if at all is because it will then lock your code to a single db flavor (mySql for example). If you ever have to migrate your code to a new environment using a different database engine, this query would probably break because of syntax differences. But any query using the ORM is supported by classes that will translate your cake $this->Model->find() calls into the proper db language commands.

So, in addition to understanding your basic issue here, you should be asking yourself if the standard ORM Queries can do what you need.

10

What version of Cake are you using?