Complex sql query (for me anyway)

Wappler General
#1

Hi Wapplers,

maybe not a wappler query (except I can’t work out how to do this query in wappler). Was hoping there was a wappler guru and sql guru who could point me in the right direction (pretty please)

The scenario. I have created a message table and a message_read table. The messages relate to jobs in the system, so you can go into a job and leave messages. An admin can reply to those messages. When the user logs back in and goes to the messages page they see their messages and it flags if its read or not by left join messages to messages_read. It all works nicely for a normal user.

Now, if an admin logs in, they need to be able to see all messages for all jobs and if THEY have read it.

But, what happens now is when I view messages as an admin I get all messages for all jobs, but, it shows the message as read even if not read by me (only shows read if another user has read it).

Its because of the left join on messages_read - as that pulls in basically that someone has read it but not necessarily me. I kind of need an extra element to that left join on an identity but thats not possible.

I want (as admin) to view the page and show all messages as unread unless I have actually read them.

1
11273×757 42.1 KB

Sure that is about all as clear as mud, not sure if its possible in Wappler.

Thanks
Darren

1 Like
#2

the sql for what I want seems to be:

SELECT 
message.id, ud.firstname, tr.description, message.create_date, mr.read

FROM 
message

INNER JOIN 
transport_request AS tr ON (tr.id = message.transport_request_id) 
LEFT JOIN messages_read AS mr ON (mr.messages_id = message.id and mr.users_id = 2) 
INNER JOIN user_data AS ud ON (ud.users_id = message.creator_id)

WHERE 
message.creator_id <> 2

so the send elements in the left join on messages_read being my Identity

#3

i have the where clause at the end because I dont need to see messages I added to the jobs, only see messages left by other users

#4

You can use custom query if you cant do with UI

#5

really? Never seen custom query in there? I tried once altering the query in the file directly but it gets over written, how do you add a custom query?
Thanks

#6

image
image745×498 23.2 KB

#7

oooh, thanks, let me give that a try :wink:

1 Like
#8

good luck @dazmaster9 :+1:

#9

it worked :slight_smile: learn something new everyday, thankyou for the steer in the right direction

1 Like
#10

spoke to soon, almost worked :wink:

it seemed to wrok for the admin user but not for me non admin user (i have a condition in my sc that runs the correct query based on user role, that part all works)

I think I am passing the Identity wrongly, my custom query is:

SELECT 
message.id, ud.firstname, tr.description, message.create_date, mr.read
, tr.id as transport_request_id, left(message.message,100) as message
FROM 
message

INNER JOIN 
transport_request AS tr ON (tr.id = message.transport_request_id) 
LEFT JOIN messages_read AS mr ON (mr.messages_id = message.id and mr.users_id = {{InSyncSecurityProvider.identity}}) 
INNER JOIN user_data AS ud ON (ud.users_id = message.creator_id)

WHERE 
message.creator_id <> {{InSyncSecurityProvider.identity}}
and tr.users_id = {{InSyncSecurityProvider.identity}}
and message.adminonly Is Null

I am assuming I have to use some other expression and not the {{Insync secu…}} part?

in a normal query it puts like :P1 etc

#11

Please check the docs:

1 Like
#12

thanks, I sorted it, missed “s” in my query name to work with the app connect :wink:

#13

assume there is no way to do sorting on the recordset like you can with a normal query built in wappler? (and paging)? as there are no options for the dir and sort etc

#14

You can add two parameters for sort and direction and assign $_GET variables to them. So then you can sort and filter by sending values to these $_GET variables.

#15

cool thats what I just figured and trying now :slight_smile: thanks

#16

Just note that you can’t use dynamic values for parameters / can’t send dynamic column names there. More info:

1 Like
#17

like this:

SELECT 
message.id, ud.firstname, tr.description, message.create_date, mr.read, 
tr.id as transport_request_id,
 left(message.message,100) as message
FROM 
message

INNER JOIN 
transport_request AS tr ON (tr.id = message.transport_request_id) 
LEFT JOIN messages_read AS mr ON (mr.messages_id = message.id and mr.users_id = :P1) 
INNER JOIN user_data AS ud ON (ud.users_id = message.creator_id)

WHERE 
message.creator_id <> :P1
order by :P2 :P3

I have seutp the P2 and P3 to be get-dir and get-sort

but now get no recordset returned

#18

See my last reply about dynamic column names sent as parameters. Also more on this topic: