Elasticsearch seach using wildcard, range and terms

I am trying to write a search DSL query on a Elasticsearch ( Version 7.1 ) index that has fields like, product_name, exported_at and category_id.

From the document I understand that I would need to use the wildcard, range to search on the date field and terms to filter the required category_id.

Example: I need to filter the data whose SQL would be:

SELECT * from products table
WHERE product_name LIKE '%geography%'
AND exported_at >= '2020-01-01' AND exported_at <= '2020-12-31'
AND category_id IN [10,20,30,40]

Updated code below:

// Creating new index
$params = [
    'index' => PS_ELASTIC_INDEX,
    'body' => [
        'mappings' => [
            'properties' => ['wc_product_name' => ["type" => "wildcard"]]
        ],
    ]
];

$response_create = $this->client->indices()->create($params);

//Storing data to the Index
//$rows contain the data fetch from the DB

foreach($rows as $row ) {
    
    $data['body'][] = [
        'id'                => $row->id,
        'wc_product_name'   => $row->product_name,
        'category_id'       => $row->category_id,
        'exported_at'       => $row->exported_at,
    ]
}

//Search the Elastic search index

$keyword  = $requestVars['product_name'];

$filters = [];

$filters[] = [
    'wildcard' => [
            'wc_product_name' => [
            'value' => '*'.$keyword.'*', 
        ],
    ]
];

$filters[] = [
    "range" => [
        "exported_on" =>  [
            "gte" => $requestVars['date_from'],
            "lte" => $requestVars['date_to'],
            "boost" => 2.0
        ]
    ],
];

if(!empty($requestVars['categories'])) {
    $filters[] = [
        "terms" => [
            "category_id" => $requestVars['categories'],
            "boost" => '1.0'
        ],
    ];
}   

$params = [
    'index' => PS_ELASTIC_INDEX,
    'body' => [
        'query' => [
            'bool' => [ 'filter' => $filters ]
        ]
    ]
];

$data = $this->client->search($params);

Now the issue is that search works if a use a single word like "Geography" for the product name it works, searching for multiple words like "Asian Geography" would not work.

You're missing a bool/filter query in order to encapsulate your three conditions:

  'body' => [
      'query' => [
        'bool' => [
          'filter' => [
             [
                'wildcard' => [
                        'product_name' => [
                        'value' => "*".$keyword."*", 
                        'boost' => 1.0,
                        'rewrite' => 'constant_score'
                    ],
                ]
             ],
             [
                "range" => [
                    "exported_on" =>  [
                        "gte" => $requestVars['date_from'],
                        "lte" => $requestVars['date_to'],
                        "boost" => 2.0
                    ]
                ],
             ],
             [
                "terms" => [
                    "category_id" => [10,20,30,40],
                    "boost" => '1.0'
                ]
             ],
          ]
      ]
   ]
 ]