Using search with views

[Colin Close]

Hi All,

Can anyone tell me whether the <search> tag works with a view my efforts
with it seem to be futile. I'm using bits of code from the Paymaster app
since it does an awful lot of what I need to do.
Here's a snippet I'm sure the structure is correct but it simply will
not work.

    <dbobject name="visit-maintenance">
            <sql>SELECT visit_maintenance.* FROM visit_maintenance ORDER
BY surname</sql>
            <search field="surname" opr="halflike" casesensitive="false">
                <widget>empnamesearch</widget>
            </search>
            <search field="firstname" opr="halflike" casesensitive="false">
                <widget>empnamesearch</widget>
            </search>
        <loadall/>        
    </dbobject>

The table visit-maintenance is a view of a join between two tables on a
single field.

Any help greatly appreciated.

Regards

Colin Close

_______________________________________________
Bond-users mailing list
Bond-users@...
http://hcoop.net/cgi-bin/mailman/listinfo/bond-users

[Dru-2]

There is a way to test search queries in bonddb, to
make sure that <search> tag can work with them
in bonddb/bondsql there is a app called sql_parser_test.

It could be that the view is stopping it from working.
Can you try it with a table to test if it works fine? Or
if there are problems?

Search goes and rebuilds the SQL statement and
queries what actual fields and tables are actually
in the database.  The way views are dealt with in
postgresql vary from version to version, so if you
are creating your own sql on the fly with views
it may not work constantly across all postgresql versions.

I've now switched to using pl/pgsql  where the queries are
 built and run within the database which is a lot more powerful than
a straight <search>.
Though I am still perfecting it. I could send my sample code of that if
that helps.
<search> Is limited because it only works with straight forward
simple queries, and does not parse joins properly at all.



Colin Close wrote:

> Hi All,
>
> Can anyone tell me whether the <search> tag works with a view my efforts
> with it seem to be futile. I'm using bits of code from the Paymaster app
> since it does an awful lot of what I need to do.
> Here's a snippet I'm sure the structure is correct but it simply will
> not work.
>
>     <dbobject name="visit-maintenance">
>             <sql>SELECT visit_maintenance.* FROM visit_maintenance ORDER
> BY surname</sql>
>             <search field="surname" opr="halflike" casesensitive="false">
>                 <widget>empnamesearch</widget>
>             </search>
>             <search field="firstname" opr="halflike" casesensitive="false">
>                 <widget>empnamesearch</widget>
>             </search>
>         <loadall/>        
>     </dbobject>
>
> The table visit-maintenance is a view of a join between two tables on a
> single field.
>
> Any help greatly appreciated.
>
> Regards
>
> Colin Close
>
> _______________________________________________
> Bond-users mailing list
> Bond-users@...
> http://hcoop.net/cgi-bin/mailman/listinfo/bond-users
>  

--
Andrew Hill
treshna Enterprises Ltd
Tel: [phone]
Cel: 021 660 819
Web: www.treshna.com
IT Consultancy - Websites - Database development - Computer tech support


_______________________________________________
Bond-users mailing list
Bond-users@...
http://hcoop.net/cgi-bin/mailman/listinfo/bond-users

[Colin Close]

Hi Dru,

Thanks for your response, I'm rather embarrassed as I had made a naming
error which had prevented it from working. I am very, very interested in
your new code. When I first started using Bond I kept coming up against
the search problem and funnily enough I ended up writing a simple C
function that I called from Bond this function passed parameters to
pl/pgsql function which did the job and then passed the data back via a
table. it was messy but it worked quite well.
I absolutely agree that Bond should make greater use of pl/pgsql its
very powerful and it is also much faster particularly in client/server
situations which is after all what Bond needs to do well.
I remember writing about this previously and before I had really got to
grips with Bond.  One thing I vaguely recall is that I really would have
liked to have access to cursors but for the life of me I can't remember
why now I'll have to check my old mails.
I wonder whether you could advise on another problem I have?
The situation is that I have a dropdown that uses some live sql that
uses the value of a widget as a variable.  The form has a search
function that changes this widget after the search returns and at this
point i also need to refresh the dropdown. The trouble I'm having is
that I can't find any combination of api calls that will refresh the
dropdown after the search has returned. I would have thought that
afterpaint would have done it but it seems not. I know its a bit of a
race condition but the nature of the api calls kind of hint that this
sort of thing is possible.

Once again please send your code I'd be more than happy to test it and
debug within my limited abilities.

Regards

Colin Close

Dru wrote:

>  
> There is a way to test search queries in bonddb, to
> make sure that <search> tag can work with them
> in bonddb/bondsql there is a app called sql_parser_test.
>
> It could be that the view is stopping it from working.
> Can you try it with a table to test if it works fine? Or
> if there are problems?
>
> Search goes and rebuilds the SQL statement and
> queries what actual fields and tables are actually
> in the database.  The way views are dealt with in
> postgresql vary from version to version, so if you
> are creating your own sql on the fly with views
> it may not work constantly across all postgresql versions.
>
> I've now switched to using pl/pgsql  where the queries are
>  built and run within the database which is a lot more powerful than
> a straight <search>.
> Though I am still perfecting it. I could send my sample code of that if
> that helps.
> <search> Is limited because it only works with straight forward
> simple queries, and does not parse joins properly at all.
>
>
>
> Colin Close wrote:
>  
>> Hi All,
>>
>> Can anyone tell me whether the <search> tag works with a view my efforts
>> with it seem to be futile. I'm using bits of code from the Paymaster app
>> since it does an awful lot of what I need to do.
>> Here's a snippet I'm sure the structure is correct but it simply will
>> not work.
>>
>>     <dbobject name="visit-maintenance">
>>             <sql>SELECT visit_maintenance.* FROM visit_maintenance ORDER
>> BY surname</sql>
>>             <search field="surname" opr="halflike" casesensitive="false">
>>                 <widget>empnamesearch</widget>
>>             </search>
>>             <search field="firstname" opr="halflike" casesensitive="false">
>>                 <widget>empnamesearch</widget>
>>             </search>
>>         <loadall/>        
>>     </dbobject>
>>
>> The table visit-maintenance is a view of a join between two tables on a
>> single field.
>>
>> Any help greatly appreciated.
>>
>> Regards
>>
>> Colin Close
>>
>> _______________________________________________
>> Bond-users mailing list
>> Bond-users@...
>> http://hcoop.net/cgi-bin/mailman/listinfo/bond-users
>>  
>>    
>
>
>  

_______________________________________________
Bond-users mailing list
Bond-users@...
http://hcoop.net/cgi-bin/mailman/listinfo/bond-users