Quantcast

[pmapper-users] Search several database columns at the same time with the same search field

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[pmapper-users] Search several database columns at the same time with the same search field

Pedro Venâncio
Hi,

I'm trying to create a search of type suggest, that search multiple columns of a PostGIS layer, at the same time.

For example,

col1 | col2
a | b
c | d
e | f
...


I tried this:

<searchitem name="n_casas" description="Casas">
    <layer type="postgis" name="tipo">
        <field type="s" name="col1" alias="nomes" description="Nome" wildcard="0" operator="OR">
            <definition type="suggest" connectiontype="db" minlength="0" sort="asc">
                <dsn encoding="UTF-8">pgsql://xxx:xxx@localhost/cartografia</dsn>
                <sql>SELECT DISTINCT col1 FROM xxx.casas WHERE col1 ~* '[search]' ORDER BY col1</sql>
            </definition>
        </field>
        <field type="s" name="col2" alias="nomes" operator="OR">
            <definition type="suggest" connectiontype="db" minlength="0" sort="asc">
                <dsn encoding="UTF-8">pgsql://xxx:xxx@localhost/cartografia</dsn>
                <sql>SELECT DISTINCT col2 FROM xxx.casas WHERE col2 ~* '[search]' ORDER BY col2</sql>
            </definition>
        </field>
    </layer>
</searchitem>


This way I can search and get the correct result. However, two fields are displayed to do the search (image attached), and I want to just get one field, where it does the search for the values ​​of the two columns (col1 and col2).

Any suggestions?

Thank you very much!

Best regards,
Pedro

------------------------------------------------------------------------------
Open source business process management suite built on Java and Eclipse
Turn processes into business applications with Bonita BPM Community Edition
Quickly connect people, data, and systems into organized workflows
Winner of BOSSIE, CODIE, OW2 and Gartner awards
http://p.sf.net/sfu/Bonitasoft
_______________________________________________
pmapper-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/pmapper-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [pmapper-users] Search several database columns at the same time with the same search field

Pedro Venâncio
Sorry, I forgot to attach the image. Here it is.


Best regards,
Pedro




----- Mensagem original -----

> DE: Pedro
> Assunto: Search several database columns at the same time with the same search field
>
> Hi,
>
> I'm trying to create a search of type suggest, that search multiple columns
> of a PostGIS layer, at the same time.
>
> For example,
>
> col1 | col2
> a | b
> c | d
> e | f
> ...
>
>
> I tried this:
>
> <searchitem name="n_casas" description="Casas">
>     <layer type="postgis" name="tipo">
>         <field type="s" name="col1"
> alias="nomes" description="Nome" wildcard="0"
> operator="OR">
>             <definition type="suggest"
> connectiontype="db" minlength="0" sort="asc">
>                 <dsn
> encoding="UTF-8">pgsql://xxx:xxx@localhost/cartografia</dsn>
>                 <sql>SELECT DISTINCT col1 FROM xxx.casas WHERE col1 ~*
> '[search]' ORDER BY col1</sql>
>             </definition>
>         </field>
>         <field type="s" name="col2"
> alias="nomes" operator="OR">
>             <definition type="suggest"
> connectiontype="db" minlength="0" sort="asc">
>                 <dsn
> encoding="UTF-8">pgsql://xxx:xxx@localhost/cartografia</dsn>
>                 <sql>SELECT DISTINCT col2 FROM xxx.casas WHERE col2 ~*
> '[search]' ORDER BY col2</sql>
>             </definition>
>         </field>
>     </layer>
> </searchitem>
>
>
> This way I can search and get the correct result. However, two fields are
> displayed to do the search (image attached), and I want to just get one field,
> where it does the search for the values ​​of the two columns (col1 and col2).
>
> Any suggestions?
>
> Thank you very much!
>
> Best regards,
> Pedro
>
------------------------------------------------------------------------------
Open source business process management suite built on Java and Eclipse
Turn processes into business applications with Bonita BPM Community Edition
Quickly connect people, data, and systems into organized workflows
Winner of BOSSIE, CODIE, OW2 and Gartner awards
http://p.sf.net/sfu/Bonitasoft
_______________________________________________
pmapper-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/pmapper-users

search.jpg (9K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [pmapper-users] Search several database columns at the same time with the same search field

Pedro Venâncio
In reply to this post by Pedro Venâncio
Hi,

Do you think this is possible just configuring the search in the config_xxx.xml? I've tried several ways, but without success.


Thanks.

Best regards,
Pedro





----- Mensagem original -----

> DE: Pedro
>
> Hi,
>
> I'm trying to create a search of type suggest, that search multiple columns
> of a PostGIS layer, at the same time.
>
> For example,
>
> col1 | col2
> a | b
> c | d
> e | f
> ...
>
>
> I tried this:
>
> <searchitem name="n_casas" description="Casas">
>     <layer type="postgis" name="tipo">
>         <field type="s" name="col1"
> alias="nomes" description="Nome" wildcard="0"
> operator="OR">
>             <definition type="suggest"
> connectiontype="db" minlength="0" sort="asc">
>                 <dsn
> encoding="UTF-8">pgsql://xxx:xxx@localhost/cartografia</dsn>
>                 <sql>SELECT DISTINCT col1 FROM xxx.casas WHERE col1 ~*
> '[search]' ORDER BY col1</sql>
>             </definition>
>         </field>
>         <field type="s" name="col2"
> alias="nomes" operator="OR">
>             <definition type="suggest"
> connectiontype="db" minlength="0" sort="asc">
>                 <dsn
> encoding="UTF-8">pgsql://xxx:xxx@localhost/cartografia</dsn>
>                 <sql>SELECT DISTINCT col2 FROM xxx.casas WHERE col2 ~*
> '[search]' ORDER BY col2</sql>
>             </definition>
>         </field>
>     </layer>
> </searchitem>
>
>
> This way I can search and get the correct result. However, two fields are
> displayed to do the search (image attached), and I want to just get one field,
> where it does the search for the values ​​of the two columns (col1 and col2).
>
> Any suggestions?
>
> Thank you very much!
>
> Best regards,
> Pedro
>

------------------------------------------------------------------------------
_______________________________________________
pmapper-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/pmapper-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [pmapper-users] Search several database columns at the same time with the same search field

Armin Burger-2
I would guess something like that (haven't fully understood what you
want, though...) could be achieved via the "sql_where" definition in the
XML file,

I found an old sample like

<searchitem name="cmeu01" description="Communes">
   <layer type="postgis" name="cmeu01">
     <field type="s" name="cmbname" description="Name" wildcard="1"/>
     <field type="n" name="id" description="Commune ID"/>
     <sql_from>cmeu01</sql_from>
     <sql_where>(cmbname ~* '[cmbname]' OR cminame ~* '[cmbname]') AND
id = [id]</sql_where>
   </layer>
</searchitem>


If the <sql_from> tag is really used I don't remember any more.


armin


On 07/11/2014 09:08 PM, Pedro Venâncio wrote:

> Hi,
>
> Do you think this is possible just configuring the search in the config_xxx.xml? I've tried several ways, but without success.
>
>
> Thanks.
>
> Best regards,
> Pedro
>
>
>
>
>
> ----- Mensagem original -----
>> DE: Pedro
>>
>> Hi,
>>
>> I'm trying to create a search of type suggest, that search multiple columns
>> of a PostGIS layer, at the same time.
>>
>> For example,
>>
>> col1 | col2
>> a | b
>> c | d
>> e | f
>> ...
>>
>>
>> I tried this:
>>
>> <searchitem name="n_casas" description="Casas">
>>      <layer type="postgis" name="tipo">
>>          <field type="s" name="col1"
>> alias="nomes" description="Nome" wildcard="0"
>> operator="OR">
>>              <definition type="suggest"
>> connectiontype="db" minlength="0" sort="asc">
>>                  <dsn
>> encoding="UTF-8">pgsql://xxx:xxx@localhost/cartografia</dsn>
>>                  <sql>SELECT DISTINCT col1 FROM xxx.casas WHERE col1 ~*
>> '[search]' ORDER BY col1</sql>
>>              </definition>
>>          </field>
>>          <field type="s" name="col2"
>> alias="nomes" operator="OR">
>>              <definition type="suggest"
>> connectiontype="db" minlength="0" sort="asc">
>>                  <dsn
>> encoding="UTF-8">pgsql://xxx:xxx@localhost/cartografia</dsn>
>>                  <sql>SELECT DISTINCT col2 FROM xxx.casas WHERE col2 ~*
>> '[search]' ORDER BY col2</sql>
>>              </definition>
>>          </field>
>>      </layer>
>> </searchitem>
>>
>>
>> This way I can search and get the correct result. However, two fields are
>> displayed to do the search (image attached), and I want to just get one field,
>> where it does the search for the values ​​of the two columns (col1 and col2).
>>
>> Any suggestions?
>>
>> Thank you very much!
>>
>> Best regards,
>> Pedro
>>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> pmapper-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/pmapper-users
>

------------------------------------------------------------------------------
_______________________________________________
pmapper-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/pmapper-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [pmapper-users] Search several database columns at the same time with the same search field

Pedro Venâncio
Hi Armin,

Thank you very much for your help!

My goal is to have only one search form field, and use the value inserted there to do the search in two columns at the same time. That is, search the same value in both columns.

For example, this table

id | col1 | col2
1 | 2001 | 2005
2 | 2006 | 2008
3 | 2007 | 2011
4 | 2008 | 2013
...

I want to something like this query

SELECT id, col_1, col2 FROM table
WHERE col_1 ilike '%2008%' OR col_2 ilike '%2008%'
ORDER BY col_1

to obtain this result

id | col1 | col2
2 | 2006 | 2008
4 | 2008 | 2013


Do you think this is possible?


Thanks!

Best regards,
Pedro

------------------------------------------------------------------------------
_______________________________________________
pmapper-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/pmapper-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [pmapper-users] Search several database columns at the same time with the same search field

Suman
I think you should do this

SELECT DISTINCT id FROM table
WHERE col_1 ilike '^[search]' OR col_2 ilike '^[search]'
ORDER BY col_1

to obtain your expected result.

and this will only show the id in suggest list.

i think this will help you. Enjoy

suman

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [pmapper-users] Search several database columns at the same time with the same search field

Pedro Venâncio
In reply to this post by Armin Burger-2
Hi

I tried in several ways, but without success, because I had to get the content of col1 and col2.

Then I remembered to do it the right way and normalized the database. So now it is working with a FULL OUTER JOIN.

Thanks for your support!


Best regards,
Pedro


------------------------------------------------------------------------------
Want fast and easy access to all the code in your enterprise? Index and
search up to 200,000 lines of code with a free copy of Black Duck
Code Sight - the same software that powers the world's largest code
search on Ohloh, the Black Duck Open Hub! Try it now.
http://p.sf.net/sfu/bds
_______________________________________________
pmapper-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/pmapper-users
Loading...