I am trying to add a rank column to a table. (rank.JPG image attached)
This query gives me the desired results in PhPMyAdmin, on my website.
select * from ( select user_name, city,state, total , case when total = @prev_tot and @prev_tot := total then @rnk when @prev_tot := total then @rnk := @rnk + 1 end as rnk from ( SELECT user_names.user_name,city.city,state.state,SUM(events_full.amount) AS total FROM user_names,city,state,events_full WHERE user_names.user_id=events_full.user_id AND city.city_id=events_full.city_id AND state.state_id=events_full.state_id AND events_full.season_id=13 AND amount > 0 Group By user_names.user_name order by total desc ) q1 cross join (select @rnk:=0, @prev_tot:=0) v ) q2
I tried to make a table using that query, with and without server side processing turned on. I get the error shown in error.JPG. (also attached)
My question is, is this even possible to do in WPDatatables?
If not, it's not that important to me, but I was hoping to make several of the members happy, because they have asked for this.
I am sorry to disappoint you, but as per our documentation - using variables ( @ ), stored procedures and nested queries is not supported, but you can prepare a MySQL view (which will return the data you need; call it “view1” for example, and then build a wpDataTable based on a simple query like “SELECT * FROM view1”.
So, basically, in PHPMyAdmin you can create the view with the same query, like this:
CREATE VIEW view1 AS select *
from
(
select user_name, city,state, total
, case
when total = @prev_tot
and @prev_tot := total
then @rnk
when @prev_tot := total
then @rnk := @rnk + 1
end as rnk
from
(
SELECT user_names.user_name,city.city,state.state,SUM(events_full.amount) AS total
FROM user_names,city,state,events_full
WHERE user_names.user_id=events_full.user_id
AND city.city_id=events_full.city_id
AND state.state_id=events_full.state_id
AND events_full.season_id=13
AND amount > 0
Group By user_names.user_name
order by total desc
) q1
cross join (select @rnk:=0, @prev_tot:=0) v
) q2
And then create a new table in wpDataTables, like:
Hi,
I am trying to add a rank column to a table. (rank.JPG image attached)
This query gives me the desired results in PhPMyAdmin, on my website.
select *
from
(
select user_name, city,state, total
, case
when total = @prev_tot
and @prev_tot := total
then @rnk
when @prev_tot := total
then @rnk := @rnk + 1
end as rnk
from
(
SELECT user_names.user_name,city.city,state.state,SUM(events_full.amount) AS total
FROM user_names,city,state,events_full
WHERE user_names.user_id=events_full.user_id
AND city.city_id=events_full.city_id
AND state.state_id=events_full.state_id
AND events_full.season_id=13
AND amount > 0
Group By user_names.user_name
order by total desc
) q1
cross join (select @rnk:=0, @prev_tot:=0) v
) q2
I tried to make a table using that query, with and without server side processing turned on. I get the error shown in error.JPG. (also attached)
My question is, is this even possible to do in WPDatatables?
If not, it's not that important to me, but I was hoping to make several of the members happy, because they have asked for this.
Hello Kevin.
I am sorry to disappoint you, but as per our documentation - using variables ( @ ), stored procedures and nested queries is not supported, but you can prepare a MySQL view (which will return the data you need; call it “view1” for example, and then build a wpDataTable based on a simple query like “SELECT * FROM view1”.
So, basically, in PHPMyAdmin you can create the view with the same query, like this:
And then create a new table in wpDataTables, like:
And it should work fine.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
Thank you Aleksandar.
I tried creating a view yesterday.
It would not allow it (#1351 - View's SELECT contains a variable or parameter)
It's ok. Like I said, it's not that important. I just thought it might be worth a shot.
Thanks so much anyway.
Ah, so creating a view with a variable is also a no-no, that's good to know, thanks Kevin
You're welcome, sorry I couldn't have a more positive reply.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables