通过PHP使用postgresql的Age函数

Question

I am trying to filter my results through Age range. In my db, I have a table which has a column 'dob', so I am trying to use AGE function of postgreSql to get the age from 'dob'. But it's not working. Following is my code.

contact.php

<div class="form-group">
    <label> Filter by Age</label>
    <div class="input-group">
        <input id="start-age" type="text" name="from-age" class="form-control" placeholder="from" />
        <input id="end-age" type="text" name="to-age" class="form-control" placeholder="to" />
    </div>
</div>

<script>
    $(document).ready(function() {
        $('body').on('change', '#end-age', function () {
            var from_age = $('#start-age').val();
            var to_age = $(this).val();

            var queryString1;
            var queryString2;
            if(location.search && from_age != -1 && to_age != -1){
                queryString1 = getQueryObj(location.search);
                queryString2 = getQueryObj(location.search);
                $.each(queryString1,function(i,e){
                    queryString1[i] = decodeURIComponent(e);
                });
                $.each(queryString2,function(i,e){
                    queryString2[i] = decodeURIComponent(e);
                });
                queryString1.from_filter = from_age;
                queryString2.to_filter = to_age;

            } else if(from_age == -1 && to_age == -1){
                queryString1 = getQueryObj(location.search);
                queryString2 = getQueryObj(location.search);
                delete queryString1.from_filter;
                delete queryString2.to_filter;
            } else {
                queryString1 = {from_filter:from_age};
                queryString2 = {to_filter:to_age};
            }
            window.location.replace('/user?'+ $.param(queryString1) + '&' + $.param(queryString2));
        });
    });

</script>

contactController.php

<?php
class userController extends AdminController {

    function __construct(){
        parent::__construct("Contact","contacts");
    }

    function index(Array $params = []){

        $ageSql = "";
        if(isset($_GET['from_filter']) && isset($_GET['to_filter'])) {
            $from_age = $_GET['from_filter'];
            $to_age = $_GET['to_filter'];
            $ageSql = "SELECT id FROM user WHERE AGE(timestamp dob) >= $from_age AND AGE(timestamp dob) <= ($to_age)";
            $params['queryOptions']['where'][] = "contact_id = $ageSql";

        }
        parent::index($params);
    }
}

When I tried the above sql into Postgre, I am getting an error that Age is not a function defined. I want all the users in the range of the age user selects in the filter bar. Help is appreciated. TIA

Answer 1

Try to use now() instead of timestamp. Also if you want to compare only years use EXTRACT YEAR form AGE(), which will give you only year. Assuming your dob is of date or timestamp type:

$ageSql = "SELECT id FROM table WHERE EXTRACT(YEAR FROM AGE(now(), dob)) >= 25 AND EXTRACT(YEAR FROM AGE(now(), dob)) <= 40";
$params['queryOptions']['where'][] = "contact_id IN $ageSql"; //use 'IN' in case more than 1 row

And if 'dob' is of some varchar or text type, cast it like this- dob::timestamp