Select specific fields from query

Question

I'm trying to get tags out of the underlying craft DB and serve it as a REST endpoint - however, I'm only interested in exposing the tag id and title, so my data should look like this:

{id: 1, title: "hello"}, {id: 2, title: "test"}

I'm currently able to achieve this with the following query:

$tags = \craft\elements\Tag::find()->all();

The problem with this is that this returns all the fields the tag has. I then have to filter out the two fields I want by doing:

foreach($tags as $tag) {
   $out = [];
   $out['id'] = $tag['id'];
   $out['title'] = $tag['title'];
   return $out;
}

This is quite wasteful as I'm fetching a bunch of fields I don't need from the DB, then looping through all the tags and parsing out the two fields I need.

What I'd like to do instead is only fetch these two fields from craft in my query.

I found two possible ways of doing this - one of them uses the select method:

Something like

$tags = \craft\elements\Tag::find()->select(['id', 'title']).all();

The problem with the above code is that the SQL query which is executed by the above doesn't actually have fields called id and title due to aliasing and joins being carried out on multiple tables - therefore I'd have to know exactly which fields I want and in which table in the SQL schema they sit...this doesn't scale well for more complex queries.

I also found https://www.yiiframework.com/doc/api/2.0/yii-base-arrayable#fields()-detail which seems like it could be useful but I wasn't able to get this to work at all and the documentation isn't too helpful.

Answer 1

I tried eager loading before, and I couldn't figure out how to do it.

https://docs.craftcms.com/v3/dev/eager-loading-elements.html

Now I just query it using SQL. I know it's a bit hacking, but it gets the job done. The DB structure of craft_content table hasn't change since Craft 2 (when I started using craft), so its unlikely that future updates will break your code.

$command = Craft::$app->getDB()->createCommand("SELECT `ct`.`id`, `cc`.`title` FROM `craft_tags` as `ct` left join `craft_content` as `cc` on `ct`.`id` = `cc`.`elementId`");
$tags = $command->queryAll();

This is only a quick fix, if anyone had a better idea, just ignore this answer.