SQL Statement or Display Object in Browse View

[Mr71]

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

[kev1n]

Use SQL aggregate SUM().

[Mr71]

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 ...

Code: Select all

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

[kev1n]

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

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

[Mr71]

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

Great Kevin!!!... it works... Thanks Again!!!

[Mr71]

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

[kev1n]

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# 

[Mr71]

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 !?

[kev1n]

Try replacing JOIN with RIGHT JOIN

[Mr71]

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