如何将原生SQL查询转换为Silverstripe SQLQuery抽象层并解决查询报错

Hey there, let's get this sorted for you! That "Unknown column 'Fashion' in 'where clause'" error is likely happening because of how raw string literals are being parsed when using DB::query(). Switching to Silverstripe's SQLQuery abstraction layer not only fixes this issue but also makes your code more maintainable and aligned with Silverstripe's best practices.

Here's how to convert your raw SQL query to use SQLQuery:

use SilverStripe\ORM\Queries\SQLQuery;

// Build the query using Silverstripe's abstraction layer
$filteredStories = SQLQuery::create()
    // Define the columns we want to fetch, including aliases
    ->setSelect([
        'C.ID',
        'C.URLSegment',
        'C.Title',
        'B.Title' => 'Category' // Alias B.Title as "Category"
    ])
    // Set the base table and add our joins
    ->setFrom('articlepage_categories AS A')
    ->addJoin('articlecategory AS B', 'A.ArticleCategoryID = B.ID')
    ->addJoin('sitetree AS C', 'A.ArticlePageID = C.ID')
    // Add the WHERE condition (SQLQuery handles parameter binding automatically)
    ->addWhere(['B.Title' => 'Fashion'])
    // Limit results to 5, just like your original query
    ->setLimit(5);

// Execute the query to get the result set
$results = $filteredStories->execute();

Key Benefits of This Approach: #

• No more quoting issues: SQLQuery automatically handles parameter binding, so you don't have to worry about incorrect string escaping (which was probably causing your original error).
• Readability: The chained method calls make the query structure much clearer at a glance.
• Maintainability: It's easier to modify parts of the query (like adding more filters or joins) later on without messing with raw SQL strings.

Using the Results: #

Once you've executed the query, you can loop through the results just like you would with any database result set:

foreach ($results as $story) {
    echo "<h3>{$story['Title']}</h3>";
    echo "<p>Category: {$story['Category']}</p>";
    echo "<a href='{$story['URLSegment']}'>Read more</a>";
}

Note: If you're using Silverstripe 4 or later, make sure you have the correct namespace imported (use SilverStripe\ORM\Queries\SQLQuery;) at the top of your file.

内容的提问来源于stack exchange，提问作者Trekin