I'd like to create a chartthat includes the empty dates in the table being queried below on the same page. The queried view does include the empty dates but is being filtered on CURRENT_USER_ID. Since the empty dates tutor ID's are all NULL from a UNION of empty dates they are being filtered out.
The only workaround seems to be to create two seperate tables and hide one in the frontend interface but I would only be able to show the past month. Especially since in the chart I am not able to set a range based on the current date back two months for example.
Is this the best solution or am I missing something key here?
I have a website where tutors log on to see their tutoring sessions. I would like them to be able to quickly see at a glance a graph with the frequency of tutoring sessions of some period in the near past to present. So I have created a view which contains all sessions + all empty dates (screenshot p1).
As you can see all dates exist at least once as an empty date, this makes sure that the where clause never removes a date. This view allows me to use a WHERE tutor_id = %current_user_id% or NULL to only pull up a table with only the tutors own session + empty dates.
I was hoping to be able to show the graph including the empty dates, but show the table below excluding the empty dates, by using the filtering option CURRENT_USER_ID without it affecting the chart.
The problem is that the chart can not be sorted on a date range which is like 'last two months'. So the chart has to be sorted as the table is. This means I would have to also show the NULL rows in the visible table below the chart.
My only solution seems to be to create duplicate tables and hide the table that generates the chart, and make it load 50 rows so that the last ~two months are shown for the tutors including NULL rows. Without the NULL rows the empty dates would be removed from the chart and a tutor could not see his or her frequency of tutoring.
Is my assumption correct or am I missing something?
You are correct. Since you are hiding the empty rows from the table, the chart responds with that, so your workaround is fine - create a duplicate table, and hide it with CSS - that table will create the chart showing NULL values as well as everything else.
Do let us know if you need further assistance with hiding the table.
I did run into two additional issues trying to achieve that chart though. Due to the design of the query the chart is still showing duplicate dates (D).
1. After slightly changing the query to include SUM(COALESCE( lines, the entire webpage crashes due to a critical error. I've also tried simply just SUM or COALESCE but the webpage crashes on load then too. I noticed the syntax is red, is it not possible to include aggregate functions in the query? (shown in A & B)
2. After redesigning the tables in the database so that I don't have to use SUM or COALESCE and trying to use a new query I ran into this error (C_error) Is it possible to circumvent it while keeping the query intact?
ST_dates is added as "d", and included in the query through SELECT d.date, ST_session_graph is added as "s", but the only reference is in the LEFT JOIN. The other 4 columns from the query are not defined, whether they are coming from "d" or "s", so that could be causing the issue.
I'd like to create a chartthat includes the empty dates in the table being queried below on the same page. The queried view does include the empty dates but is being filtered on CURRENT_USER_ID. Since the empty dates tutor ID's are all NULL from a UNION of empty dates they are being filtered out.
The only workaround seems to be to create two seperate tables and hide one in the frontend interface but I would only be able to show the past month. Especially since in the chart I am not able to set a range based on the current date back two months for example.
Is this the best solution or am I missing something key here?
Hi Marco
Could you please explain in a bit more detail what you would like to achieve? Do you want to see the first screenshot (with just the chart)?
I have a website where tutors log on to see their tutoring sessions. I would like them to be able to quickly see at a glance a graph with the frequency of tutoring sessions of some period in the near past to present. So I have created a view which contains all sessions + all empty dates (screenshot p1).
As you can see all dates exist at least once as an empty date, this makes sure that the where clause never removes a date. This view allows me to use a WHERE tutor_id = %current_user_id% or NULL to only pull up a table with only the tutors own session + empty dates.
I was hoping to be able to show the graph including the empty dates, but show the table below excluding the empty dates, by using the filtering option CURRENT_USER_ID without it affecting the chart.
The problem is that the chart can not be sorted on a date range which is like 'last two months'. So the chart has to be sorted as the table is. This means I would have to also show the NULL rows in the visible table below the chart.
My only solution seems to be to create duplicate tables and hide the table that generates the chart, and make it load 50 rows so that the last ~two months are shown for the tutors including NULL rows. Without the NULL rows the empty dates would be removed from the chart and a tutor could not see his or her frequency of tutoring.
Is my assumption correct or am I missing something?
Happy Easter.
Hi Marco
You are correct. Since you are hiding the empty rows from the table, the chart responds with that, so your workaround is fine - create a duplicate table, and hide it with CSS - that table will create the chart showing NULL values as well as everything else.
Do let us know if you need further assistance with hiding the table.
Yes, hiding the table works fine,
I did run into two additional issues trying to achieve that chart though. Due to the design of the query the chart is still showing duplicate dates (D).
1. After slightly changing the query to include SUM(COALESCE( lines, the entire webpage crashes due to a critical error. I've also tried simply just SUM or COALESCE but the webpage crashes on load then too. I noticed the syntax is red, is it not possible to include aggregate functions in the query? (shown in A & B)
2. After redesigning the tables in the database so that I don't have to use SUM or COALESCE and trying to use a new query I ran into this error (C_error) Is it possible to circumvent it while keeping the query intact?
Hi Marco
ST_dates is added as "d", and included in the query through SELECT d.date,
ST_session_graph is added as "s", but the only reference is in the LEFT JOIN. The other 4 columns from the query are not defined, whether they are coming from "d" or "s", so that could be causing the issue.
Hmm afraid I get this parse error now. Is it possible to do JOIN's in the query?
Hi Marco
I don't believe this is a good LEFT JOIN. Could you please send us these two database tables, so we can try locally?
Sure, I've created a public test case for inspection here:
https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=7618e6d088a84499ede4c40868694227
As you can see there's no way for me to solve this with a view.
Hi Marco
Thank you.
Could you please send us database dump of ST_dates and ST_session_graph tables, so we can test it locally?