Query Classes in General

Doug Easterbrook doug at artsman.com
Thu Aug 25 14:33:24 UTC 2022


thanks Phil.

I had a feeling I had the wrong property —  since we don’t use those and embody them in the table class as callable functions.    I appreciate the pointer in the right direction..


Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On Aug 25, 2022, at 1:58 AM, Phil (OmnisList) <phil at pgpotter.co.uk> wrote:
> 
> Hi Das,
> 
> Just to expand the join options, at the bottom of the query window, you have a box where you can put in a where condition, so some do the joins there with a where statement.
> 
> An alternative is within the table definition, in the construct change the:
> 
> Calculate$cinst.$fromclause
> 
> which changes the whole from clause, so the table name, and following joins.
> 
> Another point is that the query column name is not limited to field names, but can include sub queries if you so wish, but in which case you need to make sure an alias is set, to name the column in the row or list.
> 
> regards
> Phil Potter
> Based in Chester in the UK.
> 
> On 25/08/2022 02:05, Doug Easterbrook via omnisdev-en wrote:
>> the structure is
>> 
>> make a table class (best if you inherit from one of your main table classes)
>> 
>> look at the $sqlclassname for the table class.  it’s probably a database table.
>> OVERRIDE that and put in you query class name.    you’ll probably need to define the query class first.
>> 
>> 
>> making the queryclass
>> 
>> 1) make a new query class
>> 2) name it something
>> 3) for field names, drag in from the schema class browser.    when done, you should see things like
>> 
>> TABLE1.  FIELD1
>> TABLE1  FIELD2
>> TABLE2. FIELDA
>> TABLE2. FIELDB
>> 
>> note, with a query class, you get to reference MORE THAN ONE TABLE and ONLY the field you want.   This is a good thing.     since you don’t have to include an entire table with large binary fields in small lookup queries (assuming you have those).
>> 
>> eg:  it is far faster to brung down a couple of columns in a table, than 300 columns in a large table.
>> 
>> 
>> 
>> then if you define the list as below, you’ll only see the columns in it that are in the queryclass
>> do list.$defineFromTableClass(‘MyTable’)
>> 
>> 
>> then read the data as you would normally ..except.  there are two tables in this, so you WILL need to add your join conditions, however you. do that.
>> 
>> I have not done it this way, but I think you. can put the joins in $extraquerytext so that it is part of the query class
>> Our methodology is to have a method we call in the table class to add in the joins for selects.
>> 
>> 
>> YES you can update and insert with query defined tables
>> 
>> 
>> for inserts:  you should have defaults in your postgres table so that if a field is missing from the query class, it gets assigned the default that you’ve out into the postgres schema
>> 
>> updates are no problem
>> 
>> 
>> EXCEPT….    for both the above, you cannot insert or update across two tables.   so you have to decide which one is the ‘main table and alter your $inset and $update code to eliminate fields of the secondary tables…..
>> 
>> 
>> 
>> we do this ALL the time.  I’m only describing it vaguely, but the  gist for the above query, is that  ….  you’d look and your $select statemet aren remove TABLE2 fields  from it.  (which is why. we wrote is a TMObjs function to do that).
>> 
>> 
>> 
>> now, why on earth would you ever want to get this tricky???
>> 
>> 
>> do list.$defineFromTableClass(‘MyTableWithCLientsAndADDressesJoinedFromTwoTables’)
>> do list.$getRecords(….)
>> 
>> 
>> 
>> do list.$setSmartList()
>> allow use to change the client name
>> 
>> do list.$dowork()
>> 
>> 
>> 
>> or, if you hace some functions that manipulate the data in a table. then you can call it like
>> 
>> 
>> do list.$doMyFunctrionOnTheDataInTheList()
>> 
>> 
>> rather than defining a second list based on a main tableclass, copying data, updating it. and copying it back to display in a headed list or something like that.   very convenient.. reduces a lot of code
>> 
>> 
>> 
>> Doug Easterbrook
>> Arts Management Systems Ltd.
>> mailto:doug at artsman.com
>> http://www.artsman.com
>> Phone (403) 650-1978
>> 
>>> On Aug 24, 2022, at 9:47 AM, Das Goravani<goravanis at gmail.com>  wrote:
>>> 
>>> 
>>> Hello,
>>> 
>>> I have not been able to find sufficient documentation on the Query Class subject in the Omnis documentation.
>>> 
>>> So I have some questions:
>>> 
>>> Is the basic use of a query class like this:
>>> 
>>> You set it up.. enter the columns from tables into the query that you want to be a part of it.
>>> 
>>> Then you define a SQL list or row in Omnis from the query class.
>>> 
>>> Then when you do a select with that query defined list you get the joined results.
>>> 
>>> That’s it right? That’s the basic use of a query class, correct?
>>> 
>>> 
>>> 
>>> Next question:
>>> 
>>> You cannot INSERT or UPDATE with a query defined list or row rather..  correct?
>>> 
>>> You cannot do Data Writes with that query defined list because it is more than one table, correct?
>>> 
>>> 
>>> So in the end a query class is just a way to do joins, selects. Instead of writing it in SQL, you use the query class. Correct?
>>> 
>>> 
>>> That’s it.
>>> 
>>> Thanks
>>> 
>>> Das Goravani
>>> _____________________________________________________________
>>> Manage your list subscriptions athttps://lists.omnis-dev.com
>>> Start a new message ->mailto:omnisdev-en at lists.omnis-dev.com  
>> _____________________________________________________________
>> Manage your list subscriptions athttps://lists.omnis-dev.com
>> Start a new message ->mailto:omnisdev-en at lists.omnis-dev.com  
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 



More information about the omnisdev-en mailing list