Tag Archives: thinkphp6

[Thinkphp6] Connect to SQL Server and use subquery to report error: when subquery is not introduced with exists, only one expression can be specified in the selection list

Connect to SQL Server in thinkphp6 and report errors using subquery

sentence

Print SQL statements

Mysql

SELECT `NickName`,`Gender`,`Mobile`,`RealName`,`Birthday` FROM `SiteCustomerSupplement` `s` 

INNER JOIN `WxUser` `u` ON `u`.`OpenId`=`s`.`OpenId` 

WHERE `s`.`OpenId` IN (SELECT DISTINCT `OpenId` FROM `WxPackagesOrder` WHERE `OrderStatus` >= 40 AND `RegionId` = 72) AND `Birthday` BETWEEN '2021-10-26' AND '2021-11-02'

SQL Server

SELECT T1.* FROM (SELECT thinkphp.*, ROW_NUMBER() OVER ( ORDER BY rand()) AS ROW_NUMBER FROM (SELECT [NickName],[Gender],[Mobile],[RealName],[Birthday] FROM [SiteCustomerSupplement] [s] 

INNER JOIN [WxUser] [u] ON [u].[OpenId]=[s].[OpenId]

WHERE [s].[OpenId] IN (SELECT T1.* FROM (SELECT thinkphp.*, ROW_NUMBER() OVER ( ORDER BY rand()) AS ROW_NUMBER FROM (SELECT DISTINCT [OpenId] FROM [WxPackagesOrder] WHERE [OrderStatus] >= 40 AND [RegionId] = 72) AS thinkphp) AS T1) AND [Birthday] BETWEEN '2021-10-26' AND '2021-11-02') AS thinkphp) AS T1

Obviously, it is much longer than the MySQL statement, mainly this paragraph

SELECT thinkphp.*, ROW_NUMBER() OVER ( ORDER BY rand()) AS ROW_NUMBER FROM

So when the problem comes, how to solve it?Just replace it with a native statement. Don’t use TP internal encapsulation.

Thinkphp6 Error: constraint violation: 1052 Column ‘id’ in where clause is ambiguous

Cause

Show a column model

<?php
declare (strict_types=1);

namespace app\model;

use think\Model;

/**
 * @mixin \think\Model
 */
class Column extends Model
{

    public function articles()
    {

        return $this->belongsToMany(Article::class, 'column_article', 'article_id', 'column_id');
    }


    public function hasArt($article_id)
    {
        return $this->articles()->where('id', $article_id)->count();
    }

}

Wrong package when using

constraint violation: 1052 Column 'id' in where clause is ambiguous...

The reason for the error is that the relationship model of ThinkPHP does not know which table ID it is,

Here, we need to know which table it belongs to when sorting, so we also need to specify the table name when using it (Note: it needs to be prefixed)

    public function hasArt($article_id)
    {
        return $this->articles()->where('lu_article.id', $article_id)->count();
    }