Laravel Sql 语法笔记

一. 复杂语句之防 SQL 注入写法

Route::get('sql-injection', function() {
    $name = "admin"; // 假设用户提交
    $password = "xx' OR 1='1"; // // 假设用户提交
    $result = DB::select(
        DB::raw("SELECT * FROM users WHERE name =:name and password = :password"),
        [
            'name' => $name,
            'password' => $password,
        ]
    );
    dd($result);
});

十. 项目随记

1) 多条件左连接

原生语句

select `blog_articles`.*, `images`.`path` as `avatar_path` from `blog_articles` left join `images` on `images`.`user_id` = `blog_articles`.`user_id` and `images`.`image_type` = 'avatar' limit 15 offset 0

laravel

$blog_articles = BlogArticle::with(['category', 'user'])
            ->select('blog_articles.*', 'images.path as avatar_path')
            ->leftJoin('images', function ($join){
                $join->on('images.user_id', '=', 'blog_articles.user_id')
                    ->where('images.image_type', '=', 'avatar');
            })->paginate();

        // $blog_articles = BlogArticle::with(['category', 'user'])->paginate();
        return view('pages.blog_articles.index', compact('blog_articles'));

2) 如何查出, A 表的所有内容,外加 A 表 id 在 B 表 a_id 出现的次数;

方法1:

SELECT A.*,COUNT(B.id) AS cishu FROM `blog_tags` AS A LEFT JOIN blog_tags_link_articles AS B ON A.id=B.tag_id WHERE 1 GROUP BY A.id

方法2:

SELECT A.*,(SELECT COUNT(*) FROM blog_tags_link_articles AS B WHERE A.id=B.tag_id) AS CISHU FROM `blog_tags` AS A
讨论数量: 0

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!