How to create mysql function

Question

i have the following sql query

SELECT id,
  title,
  total_likes, 
  IFNULL(SELECT 1 FROM 'likedata' WHERE user_id=$UID AND post_id=posts.id)0) AS is_liked 
FROM 'posts'

I want to create mysql function to make my query bit shorter,

i don't know how to wrap the second query into sql function and pass 2 variables ($UID and $PID) to function to make this query shorter and more understandable.

any help would be great, thanks in advance

sorry, it meant to be table name, i'm pretty new to sql, please pardon me — Purge Project, May 27 '18 at 07:59
It would be easier to understand if you just posted your actual query — Strawberry, May 27 '18 at 08:01

score 0 · Answer 1 · answered May 27 '18 at 08:17

0

This isn't a good use case for a function.

A more fluent way of expressing this in SQL would be to use a left outer join, e.g:

SELECT posts.id, posts.title, posts.total_likes,
  (likedata.id IS NOT NULL) AS is_liked
FROM posts
LEFT JOIN likedata ON (
  posts.id = likedata.post_id AND likedata.user_id = $UID
)

You can see this in action here.

answered May 27 '18 at 08:17

thanks for the reply, but what if i have multiple data and table, in that case the query will become very long, i want to enclose it in function to make it more manageable. – Purge Project May 27 '18 at 08:21
@PurgeProject Then you grit your teeth and write a long query. Again: this isn't a good use case for functions. – May 27 '18 at 08:40

score 0 · Answer 2 · answered May 27 '18 at 12:36

You can actually simply use exists. MySQL treats booleans as numbers, so:

SELECT p.id, p.title, p.total_likes, 
       ( EXISTS (SELECT 1 FROM likedata ld WHERE ld.post_id = p.id AND ld.user_id = $UID)
       ) as is_liked 
FROM posts p;

This seems closest to your original intent.

@duskwuff's answer is also a typical way to approach this. There is a slight different, because duplicates in likedata would result in duplicate rows in the result set using a join.

For either form, you want an index on likedata(post_id, user_id). And if you are passing $UID in, you should be using parameterized queries.

Thanks for the effort but i want to do this with functions because i know in future i need to change the query, so with mysql function it will lot easier to do. Can you please provide help with FUNCTION? — Purge Project, May 27 '18 at 15:34

How to create mysql function

2 Answers2