phpmyadmin Question posted in
The official documentation can be found here.

SQL VIEW (phpMyadmin) that uses functions

Thank to your help I made a view in my database called ‘people’ that retrieve data using three functions called ‘isUserVerified‘, ‘hasUserPicture‘ and ‘userHobbies‘ from two tables called ‘users’ and ‘user_hobbies‘:

SELECT 
    `u`.`id` AS `id`,
    `isUserVerified`(`u`.`id`) AS `verification`,
    `hasUserPicture`(`u`.id) AS `profile_picture`,
    `userHobbies`(`h`.`user_id`) AS `hobbies` 
FROM 
    `people`.`users` u
INNER JOIN
   `people`.`user_hobbies` h
ON 
   `h`.`user_id` = `u`.`id`

It returns the following output:

enter image description here

I realise that this is because I am joining on:

`h`.`user_id` = `u`.`id`

But it is not what I want. For each user I want to run the tree function and return if they are verified, have a profile picture and a hobby. I am expecting 10 users with the relative information. Can you help? Thank you

Tags:

2

Answers


  1. 0

    You can replace INNER JOIN with LEFT JOIN to see all of the users, since users table is stated on the left of the JOIN keyword, and INNER looksup for the exact match in the condition. e.g. if there’s no spesific user id inserted into the hobbies table, the related row is not returned by INNER JOIN.

    Login or Signup to reply.
  2. 0

    I don’t think you need to join to hobbies at all. Your functions are doing the work for you:

    SELECT u.id,
       isUserVerified(u.id) AS verification,
       hasUserPicture(u.id) AS profile_picture,
       userHobbies(u.id) AS hobbies 
FROM people.users u;

    Note that user-defined functions tend to slow queries down, sometimes a lot. Functions may be a good idea in some languages, but in SQL it is better to express the logic as JOINs and GROUP BYs.

    Also, there is no reason to use backticks if the identifiers don’t have “bad” characters. Unnecessary backticks just make the query harder to write and read.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search