Select distinct on database

The CMS Questions
1

Hi,
what is the right way to translate this:

SELECT COUNT(DISTINCT column) FROM table

into kirby database class?

This:

$db->table('table')->select('column')->distinct()->count();

not work…:frowning:

Thanks!

2

You can always use query with a select/insert clause:


dump($db->query('SELECT COUNT(DISTINCT column) FROM table'));

But this should work as well:

dump($table->select('column')->distinct()->all()->count());

The second example returns a number only, the first a collection object.

3

I forgot the all() part…

Thanks!

4

I often start with using the db methods, but most of the time ends up with using “raw” SQL queries in the end anyway. That’s because of two reasons:

  1. The methods does not work well with complex queries and the queries tends to be complex over time.
  2. Learning the method approach is like learning a new language. Instead of just knowing SQL, you need to know the db method “language” as well.

So my recommendation is to not stay away from SQL, because it still rocks. $db->query($sql) is the way I approach it nowdays.

5

Are not db methods a good practice against SQL injections?

6

Yes, but using the query method is fine, I think, as long as you use placeholders for user data and as long as you don’t manually concatenate user input, see also these topics:

1 Like