need help with query in db with huge row

anybody here please response

Try changing the PHP ini file's memory limit?

$keyBy = collect();

Product::where('buyer', 0)->where('cat_id',1)->chunk(10000, function($products) use ($keyBy) { 
    $keyBy->merge($products->keyBy('product_id'));
});

dd($keyBy);

This is untested.

@joshgallagher24 yes , i change ini memory limit to hight 5GB even tried with -1. same error

@Francismori7 no success brother, empty array . i just need to ask you one qus. brother. is there way i can assign every chunk to a single variable then put it to a file using file_put_content for test ?

dd($keyBy);

working only inside chunk outside its empty :) i don't have much experience. just start learning programming

$products = Product::where('buyer', 0)->where('cat_id', 1)->get();
$keyby = $products->keyBy('product_id');
//remove this  $keyby->all();
 $datas= gzcompress($keyby);
$params = base64_encode($this->_fnEncrypt($datas, $this->user->key));

Lets back up a step.. what exactly are you trying to achieve here? You have a table with 900,000 records, surely you're not trying to send all of them in an api query response...? That should be paginated/chunked.. whatever you want to call it. The data should be served in reasonable groups. I'd probably lean toward no more than 1000-2500 myself.

But then at some point in this thread you also asked about writing data to a file, so ... what are you trying to do, exactly. What's the use case / business problem you're trying to solve?

@llamorin.jason

PHP Fatal error: Out of memory (allocated 1726742528) (tried to allocate 24 bytes) in C:\laragon\www\apifind\vendor\laravel\framework\src\Illuminate\Database\Connection.php on line 341

still same

in my : Memory limit: 6000M my table has huge row around 900K

@willvincent

bro, basically i want to get all unsold ( where buyer = 0 ) row from my product table . then want to compress it in gzcompress after that i will use mcrypt for encrypted this compressed data using a hash key . this will reduce my size of data and i will send this compressed data to a url using curl .

here is my workflow

$products = Product::where('buyer', 0)->where('cat_id', 1)->get();//get all unsold product
$keyby = $products->keyBy('product_id');// assign id value as key in json . by default laravel send response in json
//remove this  $keyby->all();
 $datas= gzcompress($keyby); //compressing whole data
$datas=$this->_fnEncrypt($datas, $this->user->key);// encrypting compress data with auth user crypt_key . here i use laravel auth api guard .
$params = base64_encode($datas); //now base64 encode to this encrypted data.
 $result = $this->_sendRequest($this->user->p_url,['data' => $params]); //here i am sending this data in $params variable to my user product api url using CURL

so my problem is i am not able to get whole query result for out of memory. and i am looking for a solution using chunk then get whole result in a variable or just need a solution for out of memory problem .

@willvincent Amen Bro!

@rehanskrill , still did not answer @willvincent question

I can't believe you are seriously trying to process 900K records as a single collection.

What system wants to receive so many records in one go?

Having said that, you say 900K records, but how many records does your query return (unsold product)?

@Snapey bro, total records more then 900k unsold row is around 871k

Maybe your best course of action is to do the DB dump / gzip outside of PHP..

I'd make multiple queries and write the result in an tmp file and try to zip that file.

If php still exhausts memory i'd refer to let the os do the zip job with exec

Have a look at the phpleague's csv library - there is an example for dumping to a csv file. You could do that then compress/encrypt it. It might be faster to use your database servers native syntax - but the library might be good enough.