时间戳

首页 > 解决方案 > 在 SQL RAW 查询中获取数据，格式与我们在 Laravel 中使用预加载方法获取的格式相同

sql - 在 SQL RAW 查询中获取数据，格式与我们在 Laravel 中使用预加载方法获取的格式相同

问题描述

我一直在使用 Laravel Eloquent，现在我想编写 RAW 查询并用它获取数据。

我的查询（雄辩）是这样的：

 $jobs = CompanyJob::where('fair_id',$fair_id);
        
    $jobs = $jobs->with('company');
    // dd($jobs->toSql());
 
    if (!empty($jobIds)) {
      $jobs = $jobs->whereIn('id',$jobIds);
    }
    $jobs = $jobs->orderBy('id', 'ASC');
    if(isset($search) && !empty($search))
      $jobs = $jobs->where(function($query) use($search){
        $query->where('title','LIKE','%'.$search.'%')->OrWhere('location','LIKE','%'.$search.'%');
      });

    if (!empty($limit)) {
      $jobs = $jobs->limit($limit);
      $jobs = $jobs->offset($offset);
    }

    $jobs = $jobs->get();

此查询的结果是：

{
    "jobs": [
        {
            "id": 6381,
            "company_id": "1507",
            "fair_id": "118",
            "recruiter_id": "147143",
            "title": "Master of Arts in Entrepreneurship for the Global Hospitality and Tourism Industry",
            "description": "<p><span style=\"font-weight: 400;\">H&auml;r kan du hitta mer information om utbildningen!</span><span style=\"font-weight: 400;\"><br /><br /></span></p>",
            "job_type": "Permanent",
            "language": "English",
            "location": "Brig",
            "contact_name": null,
            "phone": null,
           
            "url": "https://www.studentum.se/skola/swiss-education-group/cesar-ritz-colleges/master-of-arts-in-entrepreneurship-for-the-global-hospitality-and-tourism-industry-969435",
            "salary": "Competitive",
            "match": "1",
            "status": null,
            "params": null,
            "is_imported": "N",
            "questionnaires_template_id": "0",
            "import_ref": null,
            "imported_from": null,
            "match_index": "N",
            "last_offset": null,
            "total_records_to_index": null,
            "created_at": "2021-02-27 17:22:24",
            "updated_at": "2021-02-27 17:22:24",
            "company": {
                "id": 1507,
                "admin_id": null,
                "fair_id": "118",
                "recruiter_id": "147136",
                "company_name": "Swiss Education Group",
              
                "company_post_code": "111",
                "company_state": "Göteborg",
                "company_country": "Sverige",
                "company_match": "1",
                "company_web_url": "https://www.studentum.se/skola/swiss-education-group/",
                "company_facebook_url": null,
                "company_youtube_url": null,
                "company_twitter_url": null,
                "company_in_url": null,
                "company_instagram_url": null,
                "company_stand_type": "Bronze",
                "description": "<p><span style=\"font-weight: 400;\">Vilka &auml;r vi?</span></p>\r\n<p>&nbsp;</p>\r\n<p><span style=\"font-weight: 400;\">Swiss Education Group (SEG) &auml;r en av v&auml;rldens ledande leverant&ouml;rer av Hotell Management, kock -och konditorutbildningar i Schweiz. V&aring;ra utbildningar &auml;r till f&ouml;r studenter som vill ha en internationell karri&auml;r som hotellchef, kock, konditor eller n&aring;got annat yrke inom just servicebranschen.</span></p>\r\n<p>&nbsp;</p>\r\n<p><span style=\"font-weight: 400;\">Vi driver totalt fyra skolor p&aring; olika campus i Schweiz:</span></p>\r\n<p><span style=\"font-weight: 400;\">- Swiss Hotel Management School</span></p>\r\n<p><span style=\"font-weight: 400;\">- Culinary Arts Academy Switzerland</span></p>\r\n<p><span style=\"font-weight: 400;\">- Hotel Institute Montreux</span></p>\r\n<p><span style=\"font-weight: 400;\">- C&eacute;sar Ritz Colleges Switzerland</span></p>\r\n<p>&nbsp;</p>\r\n<p><span style=\"font-weight: 400;\">Samarbeten med branschen</span></p>\r\n<p>&nbsp;</p>\r\n<p><span style=\"font-weight: 400;\">Samtliga skolor som ing&aring;r i v&aring;rt n&auml;tverk, &auml;r akademiskt ackrediterade och har etablerade samarbeten med m&aring;nga av branschens ledande f&ouml;retag och varum&auml;rken. N&aring;gra f&aring; exempel p&aring; samarbeten &auml;r: Four Seasons, Ritz Paris, Marriott International samt lyxrestauranger v&auml;rlden &ouml;ver. Detta ger studenten en utbildning som &auml;r direkt anknuten till vad den internationella hotellbranschen efterfr&aring;gar.</span></p>\r\n<p>&nbsp;</p>\r\n<p><span style=\"font-weight: 400;\">Swiss Education Group (SEG) &auml;r en av v&auml;rldens fr&auml;msta n&auml;tverk av skolor inom 'hospitality management'. N&auml;tverkets framg&aring;ng har lett till att den internationella hotellbranschen nu st&auml;ller allt h&ouml;gre krav p&aring; sina medarbetare. Man kan verkligen s&auml;ga att SEG har h&ouml;jt ribban f&ouml;r kvaliteten p&aring; anst&auml;llda inom hospitality management.</span></p>",
                "company_logo": "assets/images/user-id-48854_SEG-logga.png",
                "company_stand_image": "assets/images/user-id-48854_SEG-logga.png",
                "company_stand_banner": "assets/images/user-id-48854_SEG-roll.jpg",
                "dashboard_thumbnail": null,
                "company_hall": "2",
                "questionnaire_selected": "0",
                "enable_recruiters": "1",
                "enable_webinars": "0",
                "enable_goodies": "0",
                "enable_media": "1",
                "enable_jobs": "1",
                "enable_documents": "1",
                "enable_about": "1",
                "enable_auto_enrolled": "0",
                "enable_poll": "0",
                "display_order": "0",
                "created_at": "2021-02-27 16:59:10",
                "updated_at": "2021-02-27 16:59:10"
            }
        },
        {
            "id": 6480,
            "company_id": "1521",
            "fair_id": "118",
            "recruiter_id": "147575",
            "title": "Master of Arts in Entrepreneurship for the Global Hospitality and Tourism Industry",
            "description": "<p><span style=\"font-weight: 400;\">H&auml;r kan du hitta mer information om utbildningen!</span><span style=\"font-weight: 400;\">&nbsp;</span></p>",
            "job_type": "Permanent",
            "language": "English",
            "location": "Brig",
            "contact_name": null,
            "phone": null,
        
            "url": "https://www.studentum.se/skola/swiss-education-group/cesar-ritz-colleges/master-of-arts-in-entrepreneurship-for-the-global-hospitality-and-tourism-industry-969435",
            "salary": "Competitive",
            "match": "1",
            "status": null,
            "params": null,
            "is_imported": "N",
            "questionnaires_template_id": "0",
            "import_ref": null,
            "imported_from": null,
            "match_index": "N",
            "last_offset": null,
            "total_records_to_index": null,
            "created_at": "2021-03-01 00:39:05",
            "updated_at": "2021-03-01 00:39:05",
            "company": {
                "id": 1521,
                "admin_id": null,
                "fair_id": "118",
                "recruiter_id": null,
                "company_name": "Swiss Education Group",
         
                "company_post_code": "111",
                "company_state": "Malmö",
                "company_country": "Sverige",
                "company_match": "1",
                "company_web_url": "https://www.studentum.se/skola/swiss-education-group/",
                "company_facebook_url": null,
                "company_youtube_url": null,
                "company_twitter_url": null,
                "company_in_url": null,
                "company_instagram_url": null,
                "company_stand_type": "Bronze",
                "description": "<p>Vilka &auml;r vi? <br /> <br /> Swiss Education Group (SEG) &auml;r en av v&auml;rldens ledande leverant&ouml;rer av Hotell Management, kock -och konditorutbildningar i Schweiz. V&aring;ra utbildningar &auml;r till f&ouml;r studenter som vill ha en internationell karri&auml;r som hotellchef, kock, konditor eller n&aring;got annat yrke inom just servicebranschen. <br /> <br /> Vi driver totalt fyra skolor p&aring; olika campus i Schweiz: <br /> - Swiss Hotel Management School <br /> - Culinary Arts Academy Switzerland <br /> - Hotel Institute Montreux <br /> - C&eacute;sar Ritz Colleges Switzerland <br /> <br /> Samarbeten med branschen <br /> <br /> Samtliga skolor som ing&aring;r i v&aring;rt n&auml;tverk, &auml;r akademiskt ackrediterade och har etablerade samarbeten med m&aring;nga av branschens ledande f&ouml;retag och varum&auml;rken. N&aring;gra f&aring; exempel p&aring; samarbeten &auml;r: Four Seasons, Ritz Paris, Marriott International samt lyxrestauranger v&auml;rlden &ouml;ver. Detta ger studenten en utbildning som &auml;r direkt anknuten till vad den internationella hotellbranschen efterfr&aring;gar. <br /> <br /> Swiss Education Group (SEG) &auml;r en av v&auml;rldens fr&auml;msta n&auml;tverk av skolor inom 'hospitality management'. N&auml;tverkets framg&aring;ng har lett till att den internationella hotellbranschen nu st&auml;ller allt h&ouml;gre krav p&aring; sina medarbetare. Man kan verkligen s&auml;ga att SEG har h&ouml;jt ribban f&ouml;r kvaliteten p&aring; anst&auml;llda inom hospitality management. <br /> </p>",
                "company_logo": "assets/images/user-id-48854_SEG-logga.png",
                "company_stand_image": "assets/images/user-id-48854_SEG-logga.png",
                "company_stand_banner": "assets/images/user-id-48854_SEG-roll.jpg",
                "dashboard_thumbnail": null,
                "company_hall": "3",
                "questionnaire_selected": "0",
                "enable_recruiters": "1",
                "enable_webinars": "0",
                "enable_goodies": "0",
                "enable_media": "1",
                "enable_jobs": "1",
                "enable_documents": "1",
                "enable_about": "1",
                "enable_auto_enrolled": "0",
                "enable_poll": "0",
                "display_order": "0",
                "created_at": "2021-03-01 00:13:26",
                "updated_at": "2021-03-01 00:13:26"
            }
        },
        {
            "id": 6520,
            "company_id": "1528",
            "fair_id": "118",
            "recruiter_id": "147609",
            "title": "Master of Arts in Entrepreneurship for the Global Hospitality and Tourism Industry",
            "description": "<p><span style=\"font-weight: 400;\">H&auml;r kan du hitta mer information om utbildningen!</span> <span style=\"font-weight: 400;\">&nbsp;</span></p>",
            "job_type": "Permanent",
            "language": "English",
            "location": "Brig",
            "contact_name": null,
            "phone": null,
         
            "url": "https://www.studentum.se/skola/swiss-education-group/cesar-ritz-colleges/master-of-arts-in-entrepreneurship-for-the-global-hospitality-and-tourism-industry-969435",
            "salary": "Competitive",
            "match": "1",
            "status": null,
            "params": null,
            "is_imported": "N",
            "questionnaires_template_id": "0",
            "import_ref": null,
            "imported_from": null,
            "match_index": "N",
            "last_offset": null,
            "total_records_to_index": null,
            "created_at": "2021-03-01 01:29:33",
            "updated_at": "2021-03-01 01:29:33",
            "company": {
                "id": 1528,
                "admin_id": null,
                "fair_id": "118",
                "recruiter_id": null,
                "company_name": "Swiss Education Group",
        
                "company_post_code": "111",
                "company_state": "Stockholm",
                "company_country": "Sverige",
                "company_match": "1",
                "company_web_url": "https://www.studentum.se/skola/swiss-education-group/",
                "company_facebook_url": null,
                "company_youtube_url": null,
                "company_twitter_url": null,
                "company_in_url": null,
                "company_instagram_url": null,
                "company_stand_type": "Bronze",
                "description": "<p>Vilka &auml;r vi? <br /> <br /> Swiss Education Group (SEG) &auml;r en av v&auml;rldens ledande leverant&ouml;rer av Hotell Management, kock -och konditorutbildningar i Schweiz. V&aring;ra utbildningar &auml;r till f&ouml;r studenter som vill ha en internationell karri&auml;r som hotellchef, kock, konditor eller n&aring;got annat yrke inom just servicebranschen. <br /> <br /> Vi driver totalt fyra skolor p&aring; olika campus i Schweiz: <br /> - Swiss Hotel Management School <br /> - Culinary Arts Academy Switzerland <br /> - Hotel Institute Montreux <br /> - C&eacute;sar Ritz Colleges Switzerland <br /> <br /> Samarbeten med branschen <br /> <br /> Samtliga skolor som ing&aring;r i v&aring;rt n&auml;tverk, &auml;r akademiskt ackrediterade och har etablerade samarbeten med m&aring;nga av branschens ledande f&ouml;retag och varum&auml;rken. N&aring;gra f&aring; exempel p&aring; samarbeten &auml;r: Four Seasons, Ritz Paris, Marriott International samt lyxrestauranger v&auml;rlden &ouml;ver. Detta ger studenten en utbildning som &auml;r direkt anknuten till vad den internationella hotellbranschen efterfr&aring;gar. <br /> <br /> Swiss Education Group (SEG) &auml;r en av v&auml;rldens fr&auml;msta n&auml;tverk av skolor inom 'hospitality management'. N&auml;tverkets framg&aring;ng har lett till att den internationella hotellbranschen nu st&auml;ller allt h&ouml;gre krav p&aring; sina medarbetare. Man kan verkligen s&auml;ga att SEG har h&ouml;jt ribban f&ouml;r kvaliteten p&aring; anst&auml;llda inom hospitality management. <br /> </p>",
                "company_logo": "assets/images/user-id-48854_SEG-logga.png",
                "company_stand_image": "assets/images/user-id-48854_SEG-logga.png",
                "company_stand_banner": "assets/images/user-id-48854_SEG-roll.jpg",
                "dashboard_thumbnail": null,
                "company_hall": "1",
                "questionnaire_selected": "0",
                "enable_recruiters": "1",
                "enable_webinars": "0",
                "enable_goodies": "0",
                "enable_media": "1",
                "enable_jobs": "1",
                "enable_documents": "1",
                "enable_about": "1",
                "enable_auto_enrolled": "0",
                "enable_poll": "0",
                "display_order": "0",
                "created_at": "2021-03-01 01:19:58",
                "updated_at": "2021-03-01 01:19:58"
            }
        }
    ]
}

我想为此编写 RAW 查询，首先我使用了 $jobs->toSlq()但这并没有给我查询with() 所以我搜索并发现为此我必须使用

DB::enableQueryLog(); and DB::getQueryLog();

然后我得到以下结果：

array:2 [
  0 => array:3 [
    "query" => "select * from `company_jobs` where `fair_id` = ? and (`title` LIKE ? or `location` LIKE ?) order by `id` asc"
    "bindings" => array:3 [
      0 => "118"
      1 => "%the%"
      2 => "%the%"
    ]
    "time" => 4.88
  ]
  1 => array:3 [
    "query" => "select * from `companies` where `companies`.`id` in (?, ?, ?)"
    "bindings" => array:3 [
      0 => "1507"
      1 => "1521"
      2 => "1528"
    ]
    "time" => 0.27
  ]
]

我仍然无法理解如何编写查询以获取上述数据。我将如何在原始查询中获取 company.id 以及公司记录将如何与工作数据等相结合？谁能帮我在 RAW SQL 查询中编写 Eloquent 查询？我也想知道with()在 Laravel 中是如何工作的？

模型的关系是：

Job->belongsTo('Company') and
Company->hasMany('CompanyJob')

标签： sqllaraveleloquentraw

解决方案

关于如何with工作，答案是IN()SQL 语句。尽管理论上使用不同的连接或数据库引擎，但您能够让 Model 类相互关联，这是因为 Laravel 不会生成JOIN语句。（我过去曾尝试使其基于模型配置生成真正的 JOIN，并很快发现在尝试保持表名别名以保持其唯一性时事情变得棘手，但仍然能够在结果中引用它们） .

因此，$model->with('relation')将导致以下效果：

SELECT * FROM $model::$table;
SELECT * FROM $model->relation()->getTable() WHERE $model->relation()->getKeyName() IN(Model-defined foreign key results from previous query);

至于如何实际查看生成的查询，我编写了一个调试助手，它提供了这样的功能： https : //gist.github.com/kmuenkel/a6b4e34e5bf6ca44e3c85bf753111850甚至使用反射在详细信息中包含字段名称和默认值： https ://gist.github.com/kmuenkel/4a1a8a10e0f68a7e3439c4f612523ec7

一旦这些连接起来，您只需在执行查询之前调用。然后在之后的任何时候再次调用它，并转储结果：

debug_query();
$model->with(...)->get();
dd(debug_query()->toArray());

这将向您显示在点 A 和 B 之间执行的所有查询。例如，如果您将它们放在函数调用之前和之后，查询在下游某处运行，并且您希望包含从其中调用查询的跟踪详细信息，您也可以拉入 DebugTrace 代码，并将true其作为参数添加到 point-A 函数调用中。

推荐阅读