Level 73
Have you read this post? https://tray2.se/posts/use-a-view-instead-of-a-complex-eloquent-query-in-your-laravel-application
Dec 19, 2022
19
Level 1
create view in laravel not working as intended
hi, i tried implementing create view of sql in laravel but unsuccessfull
//blade php
ViewRelatorioData::all();
//model
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
class ViewRelatorioData extends Model
{
use HasFactory;
public $table = "relatorio_index_views";
}
//migration
class CreateRelatorioView extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
DB::statement($this->createView());
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
DB::statement($this->dropView());
}
/**
* Reverse the migrations.
*
* @return void
*/
private function createView(): string
{
return <<<SQL
CREATE VIEW relatorio_index_views AS
SELECT
formulario.matricula,
FROM formulario
SQL;
}
/**
* Reverse the migrations.
*
* @return void
*/
private function dropView(): string
{
return <<<SQL
DROP VIEW IF EXISTS `relatorio_index_views`;
SQL;
}
}
//controller
class RelatorioController extends Controller
{
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
$users = ViewRelatorioData::select("SELECT
formulario.matricula,
FROM formulario")
->get()
->toArray();
dd($users);
}
}
what am i doing wrong here?
Level 1
@Tray2 yes ive done as you said but didnt knew what to put on view blade
Level 73
@peterpan26 In the blade view you do just as you would when using a table, no difference at all.
I don't think you read it properly either.
When creating a database view you create a virual table so to speak.
After that you create a model for that view and query the database just like you would a table.
Here are some examples
Model: https://github.com/Tray2/mediabase/blob/main/app/Models/BookGenreView.php
Controller: https://github.com/Tray2/mediabase/blob/main/app/Http/Controllers/Books/BooksCreateController.php
View: https://github.com/Tray2/mediabase/blob/main/resources/views/books/create.blade.php
Level 1
@Tray2 thanks
Level 102
It would help us if you explained the actual problem. "It does not work" isnt a useful description
Level 1
with this code when i try open the page , says the model ViewRelatorioData was not found
Level 73
@peterpan26 That is because you haven't created it, and once again, FFS use English names for columns, and tables it will help you write cleaner code.
Level 1
what's the purpose too of using this if then in controller i have to use the laravel eloquent that doesnt work to subtract one string for another?
Level 73
@peterpan26 The purpose of using a view instead of a complex eloquent/query builder, is that you get a cleaner controller. If you need to remove parts of a string you can use SQL for that REPLACE https://www.w3schools.com/sql/func_mysql_replace.asp
From your comment, you still haven't understood the post, and you still haven't shown your data and what the expected result is.
Level 1
'costKM' => Form::query()
->join('repairs', 'repairs.carplate', '=', 'form.carplate')
->selectRaw("SUM(form.totalfuel+form.ports+repairs.cost)/SUM(form.endkm-form.kms) AS costKM")
->orderBy('form.carplate')
->groupBy('form.carplate')
->get(),
]);
i've tried this it's everything working just this, isn't it's supposed to give 7.6 in my table and its giving 5.6
Level 73
@peterpan26 What does the data look like?
Level 1
Level 73
@peterpan26 Not really what I asked for, but
This line
"SUM(form.totalfuel+form.ports+repairs.cost)/SUM(form.endkm-form.kms) AS costKM"
Should probably be this.
" (form.totalfuel + form.ports + repairs.cost) / (form.endkm - form.kms) AS costKM"
Level 1
@Tray2 you were right, it gives now 7 wich is very close to the value i want wich is , (7.6) if possible what could i do to make it the 7.6 ? if not this is already good
Level 73
@peterpan26 Show me the data and maybe we can find the correct formula.
Level 1
@Tray2 https://prnt.sc/HAdm4PGNUrYk so the sum is totalrep:90 + totaleuro: 80 + ports : 20 / totalkm : 25 = 7.6
Level 73
@peterpan26 what do you store the result as?
The query is correct it gives 7.6, but my guess is the youneed to cast it, otherwise it will give you an integer instead of a decimal
SELECT CONVERT(150, DECIMAL(5,2));
That will give 150.00.
Level 1
@Tray2 how do i append that to the code i have? " AS custoKM..."
Level 73
Please or to participate in this conversation.