Timeline of activity

Front End Components General
#1

Hi,

I’m wanting to build a timeline of client activity that combines different activities from different database tables and show them in chronological order. For example combining bookings, transactions, tasks, emails and messages for a specific client, each have a unique ID within their respective tables (ie booking_id and transaction_id)

At the moment I can do it separately using repeating rows, but not sure how to combine them. Is there a custom sql query to map different tables into one table for example have a booking_id and transaction_id map to activity_id, then the different created date columns to a activity_created column and then use that to show all the different activities in time order?

Hope you can help.

#2

If each table has some common element then yes, in other words if each table has a clientID and the client I’d is the same on the bookings table and the transactions table then it acts like a foreign key.
You could use a standard query in Wappler and bring in your first table, then just bring in another table in the same query builder.
A new popup will appear asking what type of join you want to use and what table columns correspond to eachother from the two tables.
You can just continue adding more and more tables to this as you wish as long as each has a common column of data.

#3

I joined them in a query but the issue is that the results are joined across as columns not combined underneath as new rows.

Example with join.
Transaction Table
transaction_id
transaction_clientID
transaction_name
transaction_amount
booking_created

Booking Table
booking_id
booking_clientID
booking_name
booking_status
booking_created

Joined Table (using the clientID columns to join)
transaction_id
transaction_clientID
transaction_name
transaction_amount
transaction_created
booking_id
booking_clientID
booking_name
booking_status
booking_created

The ideal outcome is to query each table and map the data to a new table so I can sort by date created and also use the repeat binding to have the dynamic field call just one table with all the different activities

Activity Table
activity_id (either booking or transaction id)
activity_name (either transaction or booking name)
activity_status (transaction amount or booking status)
activity_created (transaction or booking created)

Not sure if this is possible.

#4

Yes they would be, you would get one row with loads and loads of columns, that is normal for a join, even if you used a MySQL VIEW it would give you the same sort of result as a join does, where it gives you a single row with many many columns.

So maybe I am not really understanding what exactly you want to achieve as your end result.

Are you wanting a single table as your final result or multiple tables, and what is the sorting parameter, I assume the ClientID.

Also does your application have multiple things a single client can do, in other words

Lets say you have a single client with a clientID of 222

222 makes 3 bookings over the course of a year, and all 3 bookings are stored in the bookings table and all 3 bookings have the same clienttID of 222

222 in the same year makes 5 transactions, and all 5 transactions are stored in the transactions table and all have the same clientID of 222

222 performs 11 tasks, and all 11 tasks are stored in the tasks table and all have the same clientID of 222

222 sends 4 emails, and all 4 emails are stored in your emails table, and all have the same clientID of 222

222 sends 8 messages, and all 8 messages are stored in your messages table, and all have the same clientID of 222

So now you want to show all activity for 222 over a year in a single table on your page therefore with the above examples you will have
222 did 3 bookings
222 did 5 transactions
222 did 11 tasks
222 did 4 emails
222 did 8 messages

Which will output 31 rows in a single table. Is that the correct idea?

EDIT: @Hyperbytes is our resident MySQL expert, because if my example is correct of what you would like then I am afraid that exceeds my MySQL knowledge.

#5

Yes your example is clearer than my attempt at explaining.

I think I found a way using a custom SQL query using UNION which combines rows from different tables into a new table as separate rows, and I was able to define each column as a specific column name so they matched up for using on the frontend in repeating rows. I also had to join two columns as I have event details in a separate table to the booking details. I haven’t attempted combining more than two tables using UNION.

SELECT bookings.booking_ID AS activity_id, event.event_name AS activity_name, bookings.attendance_token AS activity_status, bookings.booking_time_start AS activity_created, ‘Booking’ AS activity_type
FROM bookings
LEFT JOIN event ON (event.event_ID = bookings.booking_eventID)
WHERE bookings.booking_clientID = :P1
UNION
SELECT transactions.transaction_ID AS activity_id, transactions.transaction_name AS activity_name, transactions.transaction_amount AS activity_status, transactions.transaction_date_time AS activity_created, ‘Transaction’ AS activity_type
FROM transactions
WHERE transactions.transaction_clientID = :P1
ORDER BY activity_created DESC

#6

Just to add late in the day, yes unions with aliases are the way to go as you detail above

#7