Clever Geek Handbook

MySQL multiple choice result depends on other choice

I have a database:

schema

For example, the user requests all posts

data with posts.post_id = 1

. Now I want to get additional data from other tables:

  • some fields from table posts

    withpost_id = 1

  • all records from the table postmeta

    withpost_id = 1

  • some fields from the table users

    that depend on posts.user_id

    wherepost_id = 1

  • all fields from the table files

    that are related topost_id = 1

  • some fields from the table terms

    where they are related topost_id = 1

In PHP I can just run 5 queries to get this information, but I think this is not the best way because MySQL can do it faster.

Refresh part:

-- select from posts
SELECT
    posts.post_id,
    posts.post_title,
    posts.post_name,
    posts.post_content,
    posts.post_status,
    posts.post_type,
    posts.post_modified_gmt,
    posts.post_date_gmt,
    posts.post_mime_type,
    posts.user_id
FROM
    posts
WHERE
    posts.post_id = 1

--select from postmeta
SELECT
    postmeta.postmeta_value,
    postmeta.postmeta_key,
    postmeta.postmeta_id
FROM
    postmeta
WHERE
    postmeta.post_id = 1


SELECT
    files.file_size,
    files.file_type,
    files.file_name,
    files.file_id
FROM
    files
INNER JOIN file_relationships ON files.file_id = files.file_id
WHERE
    file_relationships.post_id = 1

--select from terms
SELECT
    terms.term_id,
    terms.term_name,
    terms.term_slug,
    terms.term_taxonomy,
    terms.term_description,
    terms.term_group,
    terms.term_parent,
    terms.term_count
FROM
    term_relationships
INNER JOIN terms ON term_relationships.term_id = terms.term_id
WHERE
    term_relationships.post_id = 1

--select from users , users.user_id depend on posts.user_id 
SELECT
    users.user_id,
    users.user_name,
    users.user_email,
    users.user_nicename,
    users.user_password,
    users.user_displayname,
    users.user_role,
    users.user_bio
FROM
    users
WHERE
    users.user_id = 1

this is the query I am running to get information from PHP, database + data

Is there a way to do this using only MySQL?

+3


source to share


2 answers


One request is not always better than several. In this case, I recommend combining only your 1 to 1 relationships, but leaving separate queries for your relationships from a few to 1, because if there are 3 members and 2 files in one record connecting both many and 1, it will return 6 lines.

Select records, user data and post-publish data

select * from posts p
join postmeta pm on pm.post_id = p.post_id
join users u on u.user_id = p.user_id
where p.post_id = 1

Select files



select f.* from files f
join file_relationships fr on fr.file_id = f.file_id
join posts p on p.post_id = fr.post_id
where p.post_id = 1

Select terms

select t.* from terms t
join term_relationships tr on tr.term_id = t.term_id
join posts p on p.post_id = tr.post_id
where p.post_id = 1
+3


source


These are all required JOINs, modify the SELECT list as desired.

select p.post_id,
       p.post_title,
       p.user_id,
       u.user_name,
       u.user_email,
       pm.*,
       f.*
  from posts p
  join postmeta pm
    on p.post_id = pm.post_id
  join users u
    on p.user_id = u.user_id
  join file_relationships fr
    on p.post_id = fr.post_id
  join files f
    on fr.file_id = f.file_id


* indicates "all columns" from the table with the specified alias.

+1


source






More articles:


All Articles