SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: SQL Statement

  1. #1
    SitePoint Addict Bloodfest's Avatar
    Join Date
    Feb 2006
    Posts
    229
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Statement

    I'm using this statement to do a weighted search.. problem is, i need to join a table... cant figure out where to put it..

    need some help..

    Code:
    <cffunction name="search" access="remote">
        <cfset x = replace(keyword," ", ",")>
    	<cfset i = keyword & "," & x>
    	<cfset length = listLen(i)>
        <cfset row = 1>
        <cfquery name="photos" datasource="#dsn#">
        	select *, sum(relevance) as total
            from (
            select *, 10 AS relevance FROM tbl_photos where title = '#keyword#'
            union all
            select *, 8 AS relevance FROM tbl_photos where description = '#keyword#'
            union all
            select *,  AS relevance FROM tbl_photos where title like '&#37;#keyword#%'
            union all
            select *, 6 AS relevance FROM tbl_photos where description like '%#keyword#%'
            union all
            <cfloop index="i" list="#keyword#" delimiters=" ">
            select *, 4 AS relevance FROM tbl_photos where title like '%#i#%'
            union all
            select *, 2 AS relevance FROM tbl_photos where description like '%#i#%'
            <cfif row lte length>UNION ALL</cfif>
            <cfset row = row + 1>
            </cfloop>
            ) results
            group by title, description
            order by relevance desc; 
        </cfquery>
    </cffunction>
    want to join a table of tags and include it in this search with a relevance value of 1

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Bloodfest View Post
    want to join a table of tags and include it in this search with a relevance value of 1
    join to which table? and on which columns? and will there be any searching of tags? any part of your keywords must equal the tag? or the entire keyword string?

    also, you must now stop using the dreaded, evil "select star" everywhere except in the outer query
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict Bloodfest's Avatar
    Join Date
    Feb 2006
    Posts
    229
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok... i got this sql statement awhile back..

    If i use anything other than * i get an error.

    the join statement I want to join is:

    Code:
    left loin tbl_photos_tags on tbl_photos_tags.photoId = tbl_photos.photoId
    i have revised the code a bit:

    Code:
    <cffunction name="search" access="remote">
    
    	<cfset temp = replace(keyword," ", ",","all")>
        
        <cfif listLen(temp) gt 1>
        	<cfset temp = keyword & "," & replace(keyword," ", ",","all")>
        </cfif>
        
        <cfloop index="i" list="#temp#" delimiters=",">
            <cfquery name="stopwords" datasource="#dsn#">
                select count(id) as count from tbl_stopwords
                where stopWord = '#i#'
            </cfquery>
            <cfif stopwords.count neq 0>
            	<cfset x = listFind(temp,i)>
                <cfset temp = listDeleteAt(temp,x)>
            </cfif>
        </cfloop>
    
    	<cfset length = listLen(temp)>
        <cfset row = 1>
        
        <!--- TODO #### search tags SQL --->
        <cfquery name="photos" datasource="#dsn#">
        	select *, sum(relevance) as total
            from (
            select *, 10 as relevance from tbl_photos where title = '#keyword#'
            union all
            select *, 8 as relevance from tbl_photos where description = '#keyword#'
            <cfif listLen(temp) neq 0>
                union all
                <cfloop index="i" list="#temp#" delimiters=",">
                    select *, 6 as relevance from tbl_photos where title like '%#i#%'
                    union all
                    select *, 4 as relevance from tbl_photos where description like '%#i#%'
                    <cfif row lt length>union all</cfif>
                    <cfset row = row + 1>
                </cfloop>
            </cfif>
            ) results
            group by title, description
            order by total desc 
        </cfquery>
    </cffunction>
    i do want to include the tags search with a relevance of 2

    If you can think of another way to code this, i willing to listen..

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    okay, here's the thing

    you have a subquery consisting of all the SELECTs UNIONed together, each assigning a relevance score, each accessing only the photos table, each using the dreaded, evil "select star"

    obviously, if you want to search tags as well, you will need an additional SELECT in the UNION -- you don't really want to join to the tags table in all the other SELECTs, you want a separate SELECT to find the relevance of the tags

    this additional SELECT will be the only one to join to the tags table

    furthermore, you can't use "select star" because that would automatically bring in the columns from the tags table, and then that particular SELECT would have more columns than the other SELECTs in the UNION, and that isn't allowed

    this is why i said you had to stop using "select star" -- you will have to list the actual columns you need, which include title, description, and any other columns you want returned

    also, you haven't said how to check the tags for relevance yet
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •