SQL Statement or Display Object in Browse View

Questions related to customising nuBuilder Forte with JavaScript or PHP.

SQL Statement or Display Object in Browse View

Unread postby Mr71 » Tue Nov 09, 2021 1:23 am

It is possible to insert an sql statement as a column of the Browse view grid. Example: each artist has the total number of his albums (from SQL), or display the result of a Display object.

A crude solution I found is to pass the result of the Display object (sql query) into a Calc object, manually editing with the NuTotal function. I create the field in the table and at each saving the query result is updated. Instead a temporary query would be I think better ...


Thanks
Mr71
 
Posts: 19
Joined: Thu Sep 30, 2021 6:02 pm
nuBuilder Version: 4.5

Re: SQL Statement or Display Object in Browse View

Unread postby kev1n » Tue Nov 09, 2021 2:16 pm

Use SQL aggregate SUM().
kev1n
nuBuilder Team
 
Posts: 2135
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: SQL Statement or Display Object in Browse View

Unread postby Mr71 » Tue Nov 09, 2021 8:21 pm

I have the artist table with the relation to the album table. In the "Browse Form" view, I would like to have the numerical indication of the album number of that artist.
It's possible to execute a Query inside the column of the "Browse Form" view in real time that shows me this data on each Artist row, or display a sql/display field (for example a Display object like inside the edit form!?

This my situation ..
I have ...
SELECT
Count(lpcdbox_id),
artista.*
FROM
lpcdbox
JOIN artista ON lpcdbox.art_lpcdbox = artista.artista_id
WHERE
((lpcdbox.art_lpcdbox ='#RECORD_ID#'))

where.... lpcdbox is the Album table, artista is the artist join with lpcdbox on the art_lpcdbox field ....

In the Edit Form, all of this, is in a Display Object and works .....


There's a way to run/display the result of this query in each row of each artist in Browse Mode!?

many thanks in advance
Mr71
 
Posts: 19
Joined: Thu Sep 30, 2021 6:02 pm
nuBuilder Version: 4.5

Re: SQL Statement or Display Object in Browse View

Unread postby kev1n » Wed Nov 10, 2021 3:00 pm

Instead of artista.* select each column of the artista table that you want to display in the Browse form. In my example below, I added artista.name, artista.country. Replace them with your column names.
Then use a GROUP BY.
Wrap the Statement in a SELECT * FROM (...) subquery. Otherwise nuBuilder's parser will fail when using aggregate functions like COUNT, SUM in a query.

browse_settings.jpg
browse_settings.jpg (56.56 KiB) Viewed 758 times


Code: Select all
SELECT
   *
FROM
   (
      SELECT
         Count(lpcdbox_id) as albums_count,
         artista_id,
         artista.name,
         artista.country
      FROM
         lpcdbox
         JOIN
            artista
            ON lpcdbox.art_lpcdbox = artista.artista_id
      GROUP BY
         artista_id,
         artista.name,
         artista.country
   ) T



Output: Number of albums per artist

artist_albums.jpg
artist_albums.jpg (13.85 KiB) Viewed 758 times
kev1n
nuBuilder Team
 
Posts: 2135
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: SQL Statement or Display Object in Browse View

Unread postby Mr71 » Thu Nov 11, 2021 5:22 am

.... the things you can do with sql ....
... with a little more patience
It's amazing!!!...

Great Kevin!!!... it works... Thanks Again!!!
Mr71
 
Posts: 19
Joined: Thu Sep 30, 2021 6:02 pm
nuBuilder Version: 4.5

Re: SQL Statement or Display Object in Browse View

Unread postby Mr71 » Thu Nov 11, 2021 8:46 pm

there's a problem....
when i search in the browser view

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'albums_count' in 'where clause'

the edit view is empty when click on the row...


for the first problem, I tried nuSetNoSearchColumns([4]); albums_count is the fourth, but nothing ....

for the edit view I used the redirect on the copy of the form I made without modifying the query and seems work, but I don't know if it can be solved differently
Mr71
 
Posts: 19
Joined: Thu Sep 30, 2021 6:02 pm
nuBuilder Version: 4.5

Re: SQL Statement or Display Object in Browse View

Unread postby kev1n » Thu Nov 11, 2021 9:22 pm

Create a temporary table by adding this PHP Code in the BB (Before Browse) event (also amend the sql query to use your columns)

Code: Select all
$s  =  "

   SELECT
      *
   FROM
      (
        SELECT
          Count(lpcdbox_id) as albums_count,
          artista_id,
          artista.name,
          artista.country
        FROM
          lpcdbox
          JOIN
            artista
            ON lpcdbox.art_lpcdbox = artista.artista_id
        GROUP BY
          artista_id,
          artista.name,
          artista.country
      ) T


";

nuRunQuery("CREATE TABLE #TABLE_ID# ". $s);



Then, in the Browse SQL, simpy use this query:

Code: Select all
SELECT * FROM #TABLE_ID#
kev1n
nuBuilder Team
 
Posts: 2135
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: SQL Statement or Display Object in Browse View

Unread postby Mr71 » Tue Nov 16, 2021 9:43 pm

Hi Kevin....
I did some tests..

this solution it's okay, but a little problem remained.
If the artist has no titles, ... artist not appear in the Browser Form list and cannot be searched.
In the query the zero results are not shown, I guess, what can I do !?
Mr71
 
Posts: 19
Joined: Thu Sep 30, 2021 6:02 pm
nuBuilder Version: 4.5

Re: SQL Statement or Display Object in Browse View

Unread postby kev1n » Wed Nov 17, 2021 1:48 am

Try replacing JOIN with RIGHT JOIN
kev1n
nuBuilder Team
 
Posts: 2135
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: SQL Statement or Display Object in Browse View

Unread postby Mr71 » Fri Nov 26, 2021 3:04 am

kevin ... thanks for the solution, but the problem still remains when I insert new artists who naturally still have zero titles (they do not appear)
i tried to find a way to display the results equal to zero but nothing ...

this is the actual query ...

Code: Select all
SELECT
   *
FROM
   (
      SELECT
Count(lpcdbox_id) as albums_count,
         
         artista_id,
         des_artista,
         naz_nazione,
         des_generi

      FROM
         lpcdbox
         RIGHT OUTER JOIN artista ON lpcdbox.art_lpcdbox = artista.artista_id
RIGHT OUTER JOIN nazione ON nazione.nazione_id = artista.prov_artista
RIGHT OUTER JOIN generi ON generi.generi_id = lpcdbox.genere_lpcdbox
JOIN discografie ON discografie.discografie_id = disco_lpcdbox


      GROUP BY
         artista_id,
         des_artista,
         naz_nazione,
         des_generi

   ) T

order by des_artista ASC
Mr71
 
Posts: 19
Joined: Thu Sep 30, 2021 6:02 pm
nuBuilder Version: 4.5

Next

Return to Custom Code

Who is online

Users browsing this forum: No registered users and 9 guests