SQL SELECT and DELETE

@Sinnbeck i thought with the ";" u can do both in the same query. But i'm learning, so if u tell it, i trust u ^^

@Hemnyos Ah didnt notice that. I still recommend doing 2 queries as you wouldn't be able to get the result of the select anyways if it had worked:) You can wrap them in a transaction if they "depend" on each other

If you show the laravel code, we can help you

@Sinnbeck OMG i didnt think bout transaction, really thx !

@Sinnbeck

public function handle()
    {

        //DB size
        \DB::statement(
            "SELECT table_name AS 'Table',
                   ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
                   FROM information_schema.TABLES
                   WHERE table_schema = 'reporting'
                   ORDER BY (data_length + index_length) DESC"
                   );

        //Delete 'script-treemap-data'
        \DB::statement(
            "DELETE audits
                   FROM audits
                   WHERE audits.slug = 'script-treemap-data'"
                   );

        //ecart 'Score'
        \DB::statement(
            "SELECT audits.slug,
                   AVG(DISTINCT audits.score) AS moyenne, STDDEV( audits.score) AS ecart_type
                   FROM audits
                   GROUP BY audits.slug"
                  );
        }


}

The last is not complete cuz i'm testing on MYSQL first.

@Hemnyos First off I suggest learning the query builder in laravel and use that. Secondly, you are just throwing away the select results. :)

Example

\DB::table('audits')->where('slug', 'script-treemap-data')->delete();

@Sinnbeck yeah i'm not a huge fan of eloquent yet, sql was easier xD sorry and thx

@Hemnyos Ok fair enough. But as I said, your selects does nothing :) You never get the result. Are the queries perhaps meant to be just 1 big query for what to delete?

@Sinnbeck does nothing yet. I'm just testing in my sql for now. But i'll like to do it in 1 query. Select and give me the standart deviation and delete the one with low stats. like STDDEV < 30

@Hemnyos then it's all 1 query. That's how sql works :) even if you run 2 queries after each other, they have no influence on each other

Your query needs to be something like

\DB::statement(
            "DELETE audits
                   FROM audits
                   WHERE audits.slug = 'script-treemap-data'
                    AND WHERE report_id IN (SELECT id FROM reports WHERE...) 
                  "); 

@Sinnbeck oh ok i got it ! so i have to do 3 independant queries or 1 for the 3 things.

@Hemnyos Ill try explain again :)

Take this query.

 \DB::statement(
            "SELECT table_name AS 'Table',
                   ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
                   FROM information_schema.TABLES
                   WHERE table_schema = 'reporting'
                   ORDER BY (data_length + index_length) DESC"
                   );

You get some rows from the database, but as you never use it for anything, its wasted

To get the actual result, you need to tell the DB to actually select the rows

$rows =  \DB::select(
            "SELECT table_name AS 'Table',
                   ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
                   FROM information_schema.TABLES
                   WHERE table_schema = 'reporting'
                   ORDER BY (data_length + index_length) DESC"
                   );
dump($rows);

Now you have the data available in php and can use it in other queries. But for mysql, the query is already gone in both cases. So when you run the DELETE, mysql has no knowledge that you just did a SELECT.

And to actually delete based on some data, you can either use a subselect as I gave an example of before.

Or you can pass data to mysql using php

$deleteThese = [1,2,3];

\DB::statement(
            "DELETE audits
                   FROM audits
                   WHERE audits.slug = 'script-treemap-data'
                    AND WHERE report_id IN (?, ?, ?)
                  ", $deleteThese); 

@Sinnbeck Really thx i understand now, what did u mean ! and for the last i try :

  $deviation = \DB::select(
            "SELECT STDDEV(audits.score) AS ecart
                   FROM audits
                   GROUP BY audits.slug
                   ORDER BY ecart DESC"
                  );
        //dump($deviation);

        \DB::delete(
            "
                DELETE audits FROM audits WHERE ecart IN $deviation < 30
            ");
````

but the column 'ecart' dont really exist cuz its just STDDEV as ecart. so i dont know how to choose only TDDEV > 30 and delete < 30 :/

@Hemnyos You still need to select some IDS, if you want something to delete by. And the first query should only use the STDDEV in a where clause. Start by writing a query in your database editor, that returns only the rows you wish to delete. Just the plain rows.. Then once it works, post it here and I can give you some suggestions :)

@Sinnbeck

SELECT audits.id, AVG(audits.score) AS score,
STDDEV(audits.score) AS ecart
FROM audits
GROUP BY audits.id
ORDER BY ecart DESC;

And i just want to delete audits < 30 for example bout stddev

@Hemnyos That is still not filtered? No where clause. Is is because you want to filter it with php?

If you just send it directly to the DELETE query, it will delete all rows. :)

@Sinnbeck hum... if i had a where, got a syntax error :/ i do it wrong i think. U_U

@Hemnyos something like this. I removed the group by as I assume ids are unique (or did you group on the wrong thing?)

SELECT audits.id
FROM audits
WHERE STDDEV(audits.score) > 12;

@Sinnbeck I did it !

SELECT audits.id, AVG(audits.score) AS score, STDDEV(audits.score) AS ecart
FROM audits
WHERE 30 < (SELECT STDDEV(audits.score) AS ecart FROM audits)
GROUP BY audits.id;
````

@Hemnyos great. You can extract the ids with $ids = collect($deviation)->pluck('id');

@Sinnbeck

the pluck is yellow (not found in array)

@Hemnyos ah sorry I'm used to eloquent. Updated

@Sinnbeck i did this

 \DB::table('audits')->select('id')->delete();
``

@Hemnyos yeah. Something like this. Be aware that this is prone to sql injection. That's why we use the query builder or eloquent

\DB::statement(
            "DELETE audits
                   FROM audits
                   WHERE audits.slug = 'script-treemap-data'
                    AND WHERE report_id IN (". $ids->implode(','). ")
                 " ); 

@Sinnbeck oh, i just did this :

 \DB::table('audits')->select('id')->delete();

@Hemnyos So just delete every single row? No need to a select() with a delete() btw :)

@Sinnbeck yeah i just can delete the $ids so ?

@Hemnyos The query you are showing deletes everything. You arent passing it any ids, so it knows nothing about what to delete. Here is how you tell it to only delete those ids

 \DB::table('audits')->whereIn('id', $ids)->delete();

Can I suggest that you at some point watch this series. https://laracasts.com/series/laravel-8-from-scratch It is completely free and covers a lot of laravel :)

@Sinnbeck ooooh i try eloquent but i dont understand how to give him the "$ids". ok, rlly thx

@Hemnyos Just to avoid confusion (It can be quite confusing) :)

This isnt actually eloquent. It is what we call the Query builder. https://laravel.com/docs/9.x/queries

\DB::table('audits')->whereIn('id', $ids)->delete();

This is eloquent, and is a layer on top of the query builder https://laravel.com/docs/9.x/eloquent

Audit::whereIn('id', $ids)->delete();

@Sinnbeck thx for the help and thx for all of your kidness :) have a nice day !

@Hemnyos Same to you :) Hope you have fun learning mysql and laravel

@tykus a command to delete audits with a weak standard Deviation

@Hemnyos SELECT does not DELETE - why do you fetch that data?

@tykus But i want to select first to have standard deviation isnt in the database and delete when it is too low

@Hemnyos where are you using the result of the SELECT queries; you're not...

@tykus what do you mean ? i'm just trying this queries first

@Hemnyos SELECT is a read-only operation; it has no side-effects that are usable for the other query(ies)

@tykus the first select is just to read yes. But the last i want to see and delete the low results

@Hemnyos yeah... that's not what you're doing though. As mentioned several times earlier - the SELECT queries are redundant in your command.

@tykus oh ok, so i really have to wirk with eloquent. :/

@Hemnyos no, not at all; whether you use Eloquent, or Query Builder or raw SQL statements... it all becomes SQL in the end.

You are missing the earlier point (i) the queries are independent from one another (ii) the SELECT queries have no side-effects to organise/write data for the DELETE query (iii) you never use the results of the SELECT queries.

You need to write your DELETE query such that it deletes the correct record(s)

@tykus ya but the STDDEV doesnt exist in the database, its just cuz i select first and do the calcul. So, i can't delete without select (for the calcul) first. Or i can do the calcul AND delete on the same ?

@Hemnyos you can calculate the STDDEV in the DELETE query.

Again, for the umpteenth time (i) the queries are independent from one another (ii) the SELECT queries have no side-effects to organise/write data for the DELETE query (iii) you never use the results of the SELECT queries

@tykus yeh i noticed this thx ^^'

@Hemnyos and yet...

Anyway, how do determine which records to delete if this is an aggregated query result???

@tykus Again, i'm learning. But my 3 queries r independant from one another and my first select is juste here to show me my stats (size) so i'll dont use it. my second queries is to delete 1 row. and the first have to calculate and delete the low results.

but i'm not an english guy, maybe i dont understand what do you mean.