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.
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
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
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?
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
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.
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
learn something new everyday, thankyou for the steer in the right direction