How can i select a count column with database

Kirby 2 Solved ✅
1

Hi everyone,
I did this query to take the id, name and a count column but the query gives me “1” instead of an object to work with it.

That’s my code:

$db->table('tyr_licenses')
                    ->select('id, name, count(*) as hasLicence')
                    ->leftjoin('tyr_licence_font', 'tyr_licence_font.id_licence = tyr_licenses.id')
                    ->where('tyr_licence_font.id_font', '=' , 36)
                    ->group('tyr_licence.id')
                    ->all();

Do you know whats the problem?
Thanks!

2

You have to wrap count(*) within backticks:

$items = $db->table('tyr_licenses')
                    ->select('name, id, `count(*)` as hasLicense')
                    ->leftjoin('tyr_licence_font', 'tyr_licence_font.id_licence = tyr_licenses.id')
                    ->where('tyr_licence_font.id_font', '=' , 36)
                    ->group('tyr_licence.id')
                    ->all();
dump($items);
3

Hi,
I think it can be done using quotes for each coloumn in select (); This is my code

$db->table(‘tyr_licenses’)
->select(‘id’, ‘name’, ‘count(*) as hasLicence’)
->leftjoin(‘tyr_licence_font’, ‘tyr_licence_font.id_licence = tyr_licenses.id’)
->where(‘tyr_licence_font.id_font’, ‘=’ , 36)
->group(‘tyr_licence.id’)
->all();

Need to put qotes for each column in select();

Thanks

4

No, that doesn’t work.

5

Thanks! But I found the problem… I called to a non-exist table in group()… Sorry

6

Where can I find these database methods in Kirby 3’s documentation?

7

It’s a bit difficult: https://getkirby.com/docs/reference/@/classes#database

1 Like
8

Or look at K3-Docs Guide: Database

9

Those are far from complete

1 Like
10

With Kirby 3.4.0, we have started refactoring the Database classes and we will continue with this in Kirby 3.5.0. The plan is to rewrite the guide with this second refactoring step as well.