I am trying to make this so I can pass the current tour_id based on the page parameter that I am passing (where tournaments.tour.id = 18) is.
I would really like to convert it to a wappler query builder query but I can't seem to figure duplicate the query in the builder.
Thanks
WITH RankedCatches AS (
SELECT
anglers.first_name || ' ' || anglers.last_name AS angler_name,
angler_catches.length,
ROW_NUMBER() OVER (PARTITION BY anglers.angler_id ORDER BY angler_catches.length DESC) AS rank
FROM
anglers
JOIN
angler_catches ON anglers.angler_id = angler_catches.angler_id
JOIN
tournaments ON angler_catches.tour_id = tournaments.tour_id
WHERE
tournaments.tour_id = 18
)
SELECT
angler_name,
MAX(CASE WHEN rank = 1 THEN length ELSE 0 END) AS length_1,
MAX(CASE WHEN rank = 2 THEN length ELSE 0 END) AS length_2,
MAX(CASE WHEN rank = 3 THEN length ELSE 0 END) AS length_3,
(MAX(CASE WHEN rank = 1 THEN length ELSE 0 END) +
MAX(CASE WHEN rank = 2 THEN length ELSE 0 END) +
MAX(CASE WHEN rank = 3 THEN length ELSE 0 END)) AS total_length
FROM
RankedCatches
GROUP BY
angler_name
ORDER BY
total_length DESC;
Not sure from your post what the problem you are facing. But I assume your query works as you have written it? Have you just tried selecting Custom Query and pasting your working query into it?
Brad, cahtgpt and I created the "custom" query, however I can only get it to work with a static tour.id value, I would like to replace the static value with a parameter so that I can have it show the catches for the tournament page that my users are currently on.
If you added a GET param that you bind to the :P1 in your query then it will be available on the front end when you add the server action on your page.
You can then bind the data you need to it.
When I put in {{$_GET.tour_id}} once I save the Server Action it changed it automatically to $_gettour_id so it removed the brackets and the . Also I noticed it seems to break all of the other server connects where I can no longer see the input paraments.
I took a look at a query that I am using to pull the single tournament data to populate the specific page for a tournament and the query looks pretty close as seen below
Other than the actual query being a different the concept is the same, only real difference is that the 2nd one is using the built in Wappler Query Builder and the one I am having issues with is using a custom SQL query.
This is how it should be named just tour_id... no $_ should be used.
This also looks correct now.
For the value here you need to use query.tour_id not a value coming from the query manager ... the query manager is used to just set values of the query params. To access a query param you just use query.param_name_here
I don't understand what is the single query builder showing on your screenshot. Aren't you using your custom query?
Please just follow what i posted above, you can use your qustom query, why you changed it to a single query now?
But that is wrong ... you entered the query.tour_id directly in the input and this way it's considered a string, not a dynamic value.
If you don't know how to enter it in code view of the data picker then define the query param first under app connect:
Select the tour_id under query in the data picker.
If you are using nodejs the query parameter should be defined on the main/layout page, not on the content page. Otherwise it won't be available in the data picker.
It's really easy .. switch to code in the data picker and enter it:
