Thread: Search function
Hi All, I am working on an application that has a search dialog. The dialog is automatically populated with all of the available fields. It gets the field names from the views that were used on the form that the search dialog was launched from. The issue that is slowly getting unmanageable is handling the different data types. If it is a date do this, if it is a string do that and if it is a boolean do something else. I would like to remove this complexity from the application. I am hoping that there is a way given the view/column names that I can either 1) dynamically build the WHERE clause 2) dynamically build the whole query 3) dynamically build the whole query, run it and return the results Has anyone tried something like this before? -- Kind Regards, Keith
I have built search functions for web pages that build the SQL string dynamically and return the result from a function through a result set. For example, your form may have three input fields with several "options" the user can search with: Date_To: (an input field) Date From: (an input field) Destination: (a drop down list with airport codes). Your SQL would look like: "SELECT * FROM table WHERE date1 >= Date_To_variable_from_the_form AND date2 <= Date_From_variable_from_the_form AND destination = Destination_from_the_form. You would then execute the statement and return the results to your application. Is this what you were looking for? Derrick ----- Original Message ----- From: "Keith Worthington" <KeithW@narrowpathinc.com> To: <pgsql-novice@postgresql.org> Sent: Wednesday, June 22, 2005 8:55 PM Subject: [NOVICE] Search function > Hi All, > > I am working on an application that has a search dialog. The dialog is > automatically populated with all of the available fields. It gets the > field names from the views that were used on the form that the search > dialog was launched from. > > The issue that is slowly getting unmanageable is handling the different > data types. If it is a date do this, if it is a string do that and if it > is a boolean do something else. > > I would like to remove this complexity from the application. > > I am hoping that there is a way given the view/column names that I can > either > 1) dynamically build the WHERE clause > 2) dynamically build the whole query > 3) dynamically build the whole query, run it and return the results > > Has anyone tried something like this before? > > -- > Kind Regards, > Keith > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > >
Keith, I don't know what interface you are using to build your application, but this sounds like a good case for a database abstraction layer. If your interface language allows such a database abstraction layer (Class::DBI in perl, PEAR DB in PHP, others in other languages), you could almost certainly benefit from it. Then, your application need only interact with the database abstraction layer, not the database itself. You can, of course, move down a level and access the database directly, but the point is that you don't need to most of the time. So, what interface are you using to build your application? Sean On Jun 22, 2005, at 10:55 PM, Keith Worthington wrote: > Hi All, > > I am working on an application that has a search dialog. The dialog > is automatically populated with all of the available fields. It gets > the field names from the views that were used on the form that the > search dialog was launched from. > > The issue that is slowly getting unmanageable is handling the > different data types. If it is a date do this, if it is a string do > that and if it is a boolean do something else. > > I would like to remove this complexity from the application. > > I am hoping that there is a way given the view/column names that I can > either > 1) dynamically build the WHERE clause > 2) dynamically build the whole query > 3) dynamically build the whole query, run it and return the results > > Has anyone tried something like this before? > > -- > Kind Regards, > Keith > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >
>> Hi All, >> >> I am working on an application that has a search dialog. The dialog >> is automatically populated with all of the available fields. It gets >> the field names from the views that were used on the form that the >> search dialog was launched from. >> >> The issue that is slowly getting unmanageable is handling the >> different data types. If it is a date do this, if it is a string do >> that and if it is a boolean do something else. >> >> I would like to remove this complexity from the application. >> >> I am hoping that there is a way given the view/column names that I can >> either >> 1) dynamically build the WHERE clause >> 2) dynamically build the whole query >> 3) dynamically build the whole query, run it and return the results >> >> Has anyone tried something like this before? >> >> -- >> Kind Regards, >> Keith > > I have built search functions for web pages that build the SQL string > dynamically and return the result from a function through a result set. > For example, your form may have three input fields with several > "options" the user can search with: Date_To: (an input field) Date > From: (an input field) Destination: (a drop down list with airport codes). > > Your SQL would look like: "SELECT * FROM table WHERE date1 >= > Date_To_variable_from_the_form AND date2 <= > Date_From_variable_from_the_form AND destination = > Destination_from_the_form. > > You would then execute the statement and return the results to your > application. > > Is this what you were looking for? > > Derrick Derrick, I am trying to create something more dynamic. At the moment the user has a drop down list that is populated based on the view a form uses. There is a second drop down list that contains the operators (=, <, >, >=, <=, <>, LIKE). There is also a text box where the user can enter the desired data. Finally the user has the option of selecting AND, OR or NONE and specifying a second condition. The challenge is that if a user selects name = tom the code is different then if the user selects closed = true -- Kind Regards, Keith
On Jun 23, 2005, at 8:08 AM, Keith Worthington wrote:
>
>>> Hi All,
>>>
>>> I am working on an application that has a search dialog. The dialog
>>> is automatically populated with all of the available fields. It
>>> gets the field names from the views that were used on the form that
>>> the search dialog was launched from.
>>>
>>> The issue that is slowly getting unmanageable is handling the
>>> different data types. If it is a date do this, if it is a string do
>>> that and if it is a boolean do something else.
>>>
>>> I would like to remove this complexity from the application.
>>>
>>> I am hoping that there is a way given the view/column names that I
>>> can either
>>> 1) dynamically build the WHERE clause
>>> 2) dynamically build the whole query
>>> 3) dynamically build the whole query, run it and return the results
>>>
>>> Has anyone tried something like this before?
>>>
>>> --
>>> Kind Regards,
>>> Keith
>> Keith,
>> I don't know what interface you are using to build your application,
>> but this sounds like a good case for a database abstraction layer.
>> If your interface language allows such a database abstraction layer
>> (Class::DBI in perl, PEAR DB in PHP, others in other languages), you
>> could almost certainly benefit from it. Then, your application need
>> only interact with the database abstraction layer, not the database
>> itself. You can, of course, move down a level and access the
>> database directly, but the point is that you don't need to most of
>> the time.
>> So, what interface are you using to build your application?
>> Sean
>
> Sean,
>
> We are using Visual Basic v6 to build the application.
>
> I am not sure that I understand the concept of an abstraction layer..
Keith,
In perl, Class::DBI works like this:
Each database table is treated as an object. It has methods such as
"search", "insert", "delete", etc. You can assign your own methods to
it, as well. So, if you have a table like "FOO" with columns bar and
baz, you can do:
my $foo = FOO->search(bar => '231', baz => 'washington')
This will create the SQL "SELECT * FROM FOO WHERE bar=231 and baz =
'washington'" and return the result as a set of FOO objects. You can
then access the information in a FOO object by doing something like:
$foo->bar which returns 231
$foo->baz which returns 'washington'
This isn't really the neat part. The neat part is that you can
construct much more complex where clauses using helpers so that you can
take the input from your form and easily construct your where clause
dynamically like:
my $foo2 = FOO->search_where(bar => {'>' , 120}, baz =>
['cincinatti','washington','baltimore'])
which will make a SQL query like:
select * from "FOO" where bar > 120 AND BAZ IN
('cincinatti','washington','baltimore');
You can make the queries nearly arbitrarily complex, but the important
point is that you take the input from your form and just plug it into
the methods and it generates the query for you, including binding
variables, etc. There are many other tools in perl to take form input
and generate SQL. Perhaps visual basic has such tools, also?
Generating SQL is not the same as database abstraction, but generating
SQL is a necessary part of database abstraction.
See here for some random google hits on the topic:
http://joseph.randomnetworks.com/archives/2004/07/08/what-is-a-
database-abstraction-layer/
http://www.perl.com/pub/a/2002/11/27/classdbi.html
Sean