在多个表上preform全文搜索 [英] Preform Full-Text search on multiple tables

问题描述

我在我的asp.net网站搜索一个表时，工程全文搜索实现的。不过，我希望用户能够同时搜索两个完全不同的表。我想这跟以下code：

I have full-text search implemented in my asp.net site that works when searching one table. However, I want the user to be able to search two completely different tables simultaneously. I am trying this with the following code:

  public List<Article> Search(List<string> keywords)
    { 
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.Append("select [aName],[aDesc] from [Table1]  union select [bName],[bDesc] from [Table2] where");

        foreach (string item in keywords)
        {
            sqlBuilder.AppendFormat("([bName] like '%{0}%' or [bDesc] like '%{0}%') and ", item);
        }


       //foreach (string item in keywords)
        //{
            //sqlBuilder.AppendFormat("([aName] like '%{0}%' or [aDesc] like '%{0}%') and    ", item);
       //}


        string sql = sqlBuilder.ToString(0, sqlBuilder.Length - 4);
        return QueryList(sql);

    }

这code总是显示从我的第一个表中的所有记录，并且只对第二台搜索。现在，这显然是因为我没有'，其中'我在SQL语句中第一个表。我无法弄清楚如何实施'，其中'为具有不同的foreach每个表循环。有什么建议？

This code always displays all the records from my first table, and only performs the search on the second table. Now this is obviously because I don't have 'where' for my first table in the sql statement. I can't figure out how to implement the 'where' for each table with different 'foreach' loops. Any suggestions?

推荐答案

UNION 将加入两个不同的查询结果。每个查询已完成执行后，该联盟被应用，所以需要两个 WHERE 条款：

UNION will join the results of two different queries. The union is applied after each query has finished executing, so you need two WHERE clauses:

select [aName],[aDesc] from [Table1]
where ([aName] like '%{0}%' or [aDesc] like '%{0}%')

union

select [bName],[bDesc] from [Table2]
where ([bName] like '%{0}%' or [bDesc] like '%{0}%')

在code，最简单的实施将涉及单独建立这两个查询，然后一起加入他们：

The easiest implementation in your code would involve building both queries separately and then joining them together:

StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select [aName],[aDesc] from [Table1] where ");
foreach (string item in keywords)
{
    sqlBuilder.AppendFormat(
        "([aName] like '%{0}%' or [aDesc] like '%{0}%') and ", item);
}

// That last "AND" requires a boolean statement to follow
// 1=1 will always return true and thus will not affect
// the result of your WHERE clause.
sqlBuilder.Append("1 = 1 ");

sqlBuilder.Append("UNION select [bName],[bDesc] from [Table2] where ");
foreach (string item in keywords)
{
    sqlBuilder.AppendFormat(
        "([bName] like '%{0}%' or [bDesc] like '%{0}%') and ", item);
}

到替代的的foreach 循环：

sqlBuilder.Append("select [aName],[aDesc] from [Table1] where ");
sqlBuilder.Append(
    string.Join(
        " and ",
        keywords.Select( k => string.Format( 
            "([aName] like '%{0}%' or [aDesc] like '%{0}%')", k )
        .ToArray()
    )
)

sqlBuilder.Append("UNION select [bName],[bDesc] from [Table2] where ");
sqlBuilder.Append(
    string.Join(
        " and ",
        keywords.Select( k => string.Format( 
            "([bName] like '%{0}%' or [bDesc] like '%{0}%')", k )
        .ToArray()
    )
)

请注意，虽然，将是一个的非常的低效的查询。如果你有超过几百行搜索，我的的高的建议您考虑替代的方法。

Please note, though, that is going to be an extremely inefficient query. If you have more than a few hundred rows to search, I highly recommend you consider alternative approaches.

此外，你似乎是脆弱的 SQL注入攻击。除非你已经在你的洗刷手动输入事先，你应该考虑保护自己

In addition, your appear to be vulnerable to SQL Injection attacks. Unless you are already scrubbing your inputs manually beforehand, you should consider protecting yourself.

这篇关于在多个表上preform全文搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！