Jan 24, 2012

DMQL OR DMQL2 search conditions on characters or string fields


DMQL or DMQL2 search conditions on Characters or string fields


DMQL OR DMQL2 Query syntax for RETS

field-criteria   ::= ( field=field-value )
field-value  ::= string-list | string-literal | .EMPTY.
string-list  ::= string *( , string )
string       ::= string-eq | string-start | string-contains | string-char
string-eq    ::= 1*ALPHANUM
string-start ::= 1*ALPHANUM *
string-contains  ::= * 1*ALPHANUM *
string-char  ::= *ALPHANUM *(? *ALPHANUM)
string-literal   ::= "<PLAINTEXT except "> *(2" *<PLAINTEXT except "> ) "

(According to RETS 1.8.0 specification)

Suppose Remarks, Remarks1, Remarks2 and Remarks3 is any characters type field.

Examples of DMQL OR DMQL2 query language

1.
Sql server query: WHERE Remarks = 'Exact Help'

DMQL query: (Remarks=Exact Help)
Or DMQL query: (Remarks="Exact Help")

Verbal meaning:  Filter only those records where Remarks is equal to "Exact Help".

Note: DMQL string comparison is not case sensitive. That is following are equivalent:
a. (Remarks='Exact Help')
b. Remarks='EXACT HELP')

2.
Sql server query: WHERE Remarks LIKE 'Exact%'

DMQL query: (Remarks=Exact*)

Verbal meaning:  Filter only those records where Remarks start with "Exact"

3.
Sql server query: WHERE Remarks LIKE '%Help'

DMQL query: (Remarks=*Help)

Verbal meaning:  Filter only those records where Remarks end with "Help"

4.
Sql server query: WHERE Remarks LIKE '%Help%'

DMQL query: (Remarks=*Help*)

Verbal meaning:  Filter only those records where Remarks contains "Help"

5.
Sql server query: WHERE Remarks LIKE '_A_'

DMQL query: (Remarks=?A?)

Verbal meaning:  Filter only those records where Remarks has only three characters and second character is 'A'

6.
Sql server query:  WHERE  Remarks1  !=  'Exact'  AND Remarks2 = 'Help'

DMQL query: ~ (Remarks1=Exact), (Remarks2=Help)
Or DMQL query: NOT (Remarks1=Exact) AND (Remarks2=Help)

Verbal meaning:  Filter only those records where Remarks1 is not equal to "Exact" and Remarks2 is equal to "Help"  

7.
Sql server query:  WHERE  Remarks  IN  ('O',  'E', 'A', 'B', 'C', 'F')

DMQL query: (Remarks=O, E, A, B, C, F)

Verbal meaning:  Filter only those records where Remarks is any of "O", "E", "A", "B", "C", "F"

8.
Sql server query: WHERE Remarks NOT IN ('O', 'E', 'A')

DMQL query: ~ (Remarks=O, E, A)
Or DMQL query: NOT (Remarks=O, E, A)

Verbal meaning:  Filter only those records where Remarks none of "O", "E", "A"

9.
Sql server query:  WHERE  Remarks  LIKE  '%O%'  OR Remarks LIKE '%E%' OR Remarks  LIKE '%A%' 

DMQL query: (Remarks=*O*, *E*, *A*)

Verbal meaning:  Filter only those records where Remarks contains any of "O", "E", "A"

10.
Sql server query:  WHERE  Remarks  NOT  LIKE  '%O%' AND Remarks NOT LIKE '%E%' AND Remarks NOT LIKE '%A%' 

DMQL query: ~ (Remarks=*O*, *E*, *A*)

Verbal meaning:  Filter only those records where Remarks contains none any of "O", "E", "A"

11.
Sql server query:  WHERE  Remarks  LIKE  '%O%'  OR  Remarks LIKE 'E%' OR Remarks = 'A' 

DMQL query: (Remarks=*O*, E*, A)

Verbal meaning:  Filter only those records where Remarks contains "O" or start with "E" or equal to "A"

12.
Sql server query:  WHERE  Remarks1  LIKE  '%O%'  AND Remarks2 LIKE 'E%' OR Remarks3 != 'A' 

DMQL query: (Remarks1=*O*),(Remark2=E*)!~(Remark3=A)

Verbal meaning:  Filter only those records where Remarks contains "O" and start with "E" or not equal to "A"

3 comments:

  1. Really nice tutorial no doubt about it. I am having some trouble using this. I want to search city and i form the search query something like (1491=*somer*) where 1491 is the systemName of city. I want to fetch all the listings which city name is Somerset. It always returns me 0 Records Found. I am in big trouble. I badly need some help.
    Thanks.

    ReplyDelete
  2. Are you able to execute other queries.
    May be some issue in Rets server or it is not using system name. Try different combinations.

    ReplyDelete
    Replies
    1. Here is my search query $search = $rets->SearchQuery(
      'Property', // Resource
      1, // Class
      '((130=2013-10-02T16:00:00+),(213=A))', // DMQL
      array(
      'Format' => 'COMPACT-DECODED',
      'Select' => 'sysid,13,1491',
      'Count' => 1,
      'Limit' => 3
      )
      );
      i am using phrets. 130=system for last update date. 213=system name for status. It works well.
      But i went through some real state site. They execute a search query at the very first page. When someone writes down 3 or more letters in the input box, a dropdown list appears down the input box which contains handful cities started with those letters and on click any of the cities pulls the listings form that city.
      But here in my case my system name for city is 1491 and when i write (1491=somerset) it pulls all the listings in the somerset city. I am wondering how i can do the first step while writing down couple of letters will show me the name of the cities relating to those letters.
      As DMQL query suggest me to do this way (1491=*som*) it should bring up all the cities that contain 'som'. But i can't make it work. I am trying very hard over the last few days and with the very few resource in internet i find myself noob to figure it out.
      Any help would greatly be appreciated.

      Delete