How do I: Filter, based on 2nd table?

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

How do I: Filter, based on 2nd table?

Post by onoehring » 2019-12-04 13:07

Hi

I am writing some custom code to open the table view von table C but only show filtered items. But how do I tell AG which filter to use?

The database is set up like this:
C has a field that is lookup from table L which has a field that is lookup from table O.

I need to create a filter which shows all records in table C that have a certain (my filter criteria) setting in table L, in the lookup field that comes from table O.

(can this be understood?)

So: How do I open (GET parameters?, options=>?) table L in tableview with my filter?

PS: A custom SQL as filter is fine - but how do I tell AG to use my SQL to display the records instead of all?

Olaf

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: How do I: Filter, based on 2nd table?

Post by jsetzer » 2019-12-04 13:37

Hi Olaf,
did you see the $options->QueryWhere property in TABLENAME_init()-function?
Best,
Jan
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How do I: Filter, based on 2nd table?

Post by onoehring » 2019-12-04 14:26

Hi Jan,
I hoped for a quick an easy answer. No, I did not see this option .. I looked in the documentation and there this was not listed and in -ini I must have overlooked it.

Thank you very much.

Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How do I: Filter, based on 2nd table?

Post by onoehring » 2019-12-04 16:05

Hi,

I do not find any documentation for this. So I am asking: Can I use a new SQL statement there or only a WHERE clause?
As I need some JOINS in my SQL before the WHERE I am wondering.

Olaf

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: How do I: Filter, based on 2nd table?

Post by jsetzer » 2019-12-04 16:15

Hi Olaf,

docs are available online:

https://bigprof.com/appgini/help/advanc ... ename_init
https://bigprof.com/appgini/help/advanc ... ist-object

The SQL Query usually already contains all joins for each and every modeled lookup.

Jan
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How do I: Filter, based on 2nd table?

Post by onoehring » 2019-12-04 16:39

Hi Jan,

thanks, sorry for being annoying.
I found a solution which should work (will try later):

Code: Select all

WHERE
     mytable.PK IN
(Select .... HERE THE QUERY THAT SELECTS ALL PKs THAT I NEED
Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How do I: Filter, based on 2nd table?

Post by onoehring » 2019-12-05 06:26

Hi Jan,

thanks for the links, I was looking at the pdf docs only. But even at the first link the $options->QueryWhere property is missing.
Bute the code on the bottom of the page helped:

Code: Select all

	ob_start();
    $xc=get_object_vars($options);
    ksort($xc);
    print_r($xc);
    $c=ob_get_clean();
	echo "<pre>".htmlspecialchars($c)."</pre>";
	
Olaf
Last edited by onoehring on 2019-12-05 06:32, edited 1 time in total.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: How do I: Filter, based on 2nd table?

Post by jsetzer » 2019-12-05 06:31

onoehring wrote:
2019-12-05 06:26
But even at the first link the $options->QueryWhere property is missing.
Sorry, I do not understand if there is still a problem or not:

There is the documentation for the _init()-function and there is also the documentation for the $options-parameter.

Some more links:
viewtopic.php?t=2090
viewtopic.php?t=3105
viewtopic.php?t=1173

If I remember right, this has also been described in the tutorial videos in Udemy courses.

Regards,
Jan
Last edited by jsetzer on 2019-12-05 06:38, edited 1 time in total.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How do I: Filter, based on 2nd table?

Post by onoehring » 2019-12-05 06:33

Hi Jan,
yes, there is the QueryWhere option, but it's not documented in the page itself. One can find it when running the code (in _init) I added to my former posting.

Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How do I: Filter, based on 2nd table?

Post by onoehring » 2019-12-05 07:54

Hi Jan,

well, my QueryWhere works .. initially.
I added a test for $_GET in the URL to the INI and according to that value I set my QueryWhere.

The problem occurs, when I want to flip through pages or sort: Then, of course, the $_GET parameters are not available anymore and my QueryWhere is lost.

How would you suggest solving this issue?
I am thinking of using a session variable - but where (and on which event) will I clear that again? Maybe there is a way to check if the former table (before reloading) was filtered / had a QueryWhere set? And even if: How do I then remove it again.

Any suggestions / ideas?

Olaf

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: How do I: Filter, based on 2nd table?

Post by jsetzer » 2019-12-05 08:30

Yes, Olaf, it is not easy. I'm also using Session variables for that.

If you just prepend the table name to the session variable, it should work when opening a different table, for example you could name your session variables like this:

Code: Select all

$_SESSION["myfilter_table1_column1"] = "123"
$_SESSION["myfilter_table1_column2"] = "A"
On init(), only read filters for the current table and apply QueryWhere conditions.

Or you could even think about storing more SQL-like conditions (I did not test this) ...

Code: Select all

$_SESSION["myfilter_table1_column1"] = "=123"
$_SESSION["myfilter_table1_column2"] = "IN ('a', 'b', 'c')"
$_SESSION["myfilter_table1_column3"] = "BETWEEN 5 AND 10"
$_SESSION["myfilter_table2_column1"] = ">=27"
You can do amazing stuff with $options->QueryWhere and Session-Variables, have a look at my "Quickfilters" implementation:
chrome_JokKwXFfmr.png
chrome_JokKwXFfmr.png (91.19 KiB) Viewed 3681 times
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How do I: Filter, based on 2nd table?

Post by onoehring » 2019-12-05 09:14

Hi Jan,

marvelous!
I very much like the idea of prepending the session variable with the tablename. My solution works for now ... but shows my hint that the data has been prefiltered also in other parts of the application. I will probably use your suggestion to improve my solution.

Thank you very much.

Olaf

Post Reply