Category Archives: MySQL

PostgreSQL Logo

How to list database privileges in PostgreSQL

When creating a new database in PostgreSQL or when creating a new connection to an existing database from a new client, it is recommended that a dedicated database user is used.

Each database user must only be granted the permissions needed to solve it’s purpose.

The following SQL command allows you to obtain the current PostgreSQL privileges configured in the active database:

SELECT grantee, table_name, privilege_type 
FROM information_schema.role_table_grants

This command will output some like:

Sample Privileges

However, you may only execute this SQL and get the PostgreSQL privileges as an admin user.

How to build dynamic query in Laravel

Dynamic query allows us to start with a base query and extend it so that we properly filter our data. You probably already used and know what Laravel is and how to create a basic database query.

We’ll cut straight to the problem:

How do we dynamically apply query conditions in Laravel?

Let’s take a look at the following example that I consider quite easy and basic query on a Listing model that has title, description, price, images and date columns:

$title = "planet";
$listings = Listing::where(function($q) use ($title) {
$q->where('title', 'like', $title .'%');

This query will return all our documents for which the title has the form planet% (they start with planet word).

You already knew how to achieve this so let’s extend this to a condition based query generation. Follow bellow:

$title = 'planet;
$price_min = 10;
$price_max = 100;
$listings = Listing::select('title', 'description', 'price', 'images', 'date');

// we're only going to filter by title if it's available
if(isset($title) && !empty($title)) {
$listings->where(function($q) use ($title) {
$q->where('title', 'like', $title.'%');

// but there's more
// what about the price?
if(is_numeric($price_min)) {
$listings->where('price', '>=', $price_min);

We’ve now learned how to build a dynamic query and how to add conditions on the run.

So how do we view our results? 1 line:

$listings = $listings->latest()->get();

That’s it. Now go and build you great app!