When building more complex queries using db_select() you will often want to see the exact SQL being generated. This is helpful for understanding the query or simply debugging the results.
This article will show you how to see the exact query being generated with db_select() as well as getting how to get all values passed as arguments. The code in this article will be applicable for both Drupal 7 and Drupal 8.
Let’s see some examples on how we would build a simple query.
Example #1: using db_select() and creating the query dynamically:
-
$results = db_select(‘node’, ‘n’)
-
->fields(‘n’)
-
->condition(‘n.type’, ‘page’)
-
->execute();
Example #2: you could also use db_query() instead. The above query would translate into the following:
-
$results = db_query(“SELECT * FROM {node} n WHERE n.type = :type”, array(
-
‘:type’ => ‘page’,
-
));
Both queries above result in the following SQL:
-
SELECT * FROM node n WHERE n.type = ‘page’;
The example #1 is preferred way of building queries because you can alter them dynamically and “tag” the queries to provide additional functionality (e.g. node_access). However, the approach #2 has two positives:
- Performance. It will be faster than queries built using
db_select(). - Readability. You are writing the exact SQL code with some Drupal added extras, such as automatic table prefixes (wrapping table names in curly brackets) and placeholders (keywords starting with colons).
To aid you with readability, there are 2 methods you will find useful:
__toString()which translates your query into something that could be plugged indb_query(). Do note that the placeholder replacement values will not be included here.arguments()which will return the array of arguments with their values.
Finally, here’s the code you can use to see the exact SQL:
-
$query = db_select(‘node’, ‘n’)
-
->fields(‘n’)
-
->condition(‘n.type’, ‘page’);
-
// The following method is manual way to see the query.
-
//
-
// Prints the query with placeholders as a string.
-
dpm($query->__toString());
-
// Print the provided arguments, so you can see exactly which values are passed.
-
dpm($query->arguments());
UPDATE: Anton Sidashin made a great point of using dpq() which is a helper function provided by the Devel module. It automatically prints (or returns) the query and replaces all arguments. To use it, just pass the pre-executed query as the first argument. Setting the second argument to TRUE will make the function return the value instead of printing it out.
-
$query = db_select(‘node’, ‘n’)
-
->fields(‘n’)
-
->condition(‘n.type’, ‘page’);
-
// The following method will get you the compiled end query with all values
-
// passed as arguments.
-
dpq($query);
Two notes for the end:
- You can call
dpq(),__toString()andarguments()only before the query has been executed. - The
db_select()queries are just provided as examples. You will most likely want to tag the query withnode_accessso the query results can be filtered to include only the nodes current user has access to. Since this is outside of the scope of the article, I did not include it in the above samples.
See also:
db_select()for D7 and D8SelectQuery::arguments()Query::__toString()for D7 and D8- Query alteration / tagging
dpq()for D7 and D8
Visit website 3501
Visit website 3502
Visit website 3503
Visit website 3504
Visit website 3505
Visit website 3506
Visit website 3507
Visit website 3508
Visit website 3509
Visit website 3510
Visit website 3511
Visit website 3512
Visit website 3513
Visit website 3514
Visit website 3515
Visit website 3516
Visit website 3517
Visit website 3518
Visit website 3519
Visit website 3520
Visit website 3521
Visit website 3522
Visit website 3523
Visit website 3524
Visit website 3525
Visit website 3526
Visit website 3527
Visit website 3528
Visit website 3529
Visit website 3530
Visit website 3531
Visit website 3532
Visit website 3533
Visit website 3534
Visit website 3535
Visit website 3536
Visit website 3537
Visit website 3538
Visit website 3539
Visit website 3540
Visit website 3541
Visit website 3542
Visit website 3543
Visit website 3544
Visit website 3545
Visit website 3546
Visit website 3547
Visit website 3548
Visit website 3549
Visit website 3550
Visit website 3551
Visit website 3552
Visit website 3553
Visit website 3554
Visit website 3555
Visit website 3556
Visit website 3557
Visit website 3558
Visit website 3559
Visit website 3560
Visit website 3561
Visit website 3562
Visit website 3563
Visit website 3564
Visit website 3565
Visit website 3566
Visit website 3567
Visit website 3568
Visit website 3569
Visit website 3570
Visit website 3571
Visit website 3572
Visit website 3573
Visit website 3574
Visit website 3575
Visit website 3576
Visit website 3577
Visit website 3578
Visit website 3579
Visit website 3580
Visit website 3581
Visit website 3582
Visit website 3583
Visit website 3584
Visit website 3585
Visit website 3586
Visit website 3587
Visit website 3588
Visit website 3589
Visit website 3590
Visit website 3591
Visit website 3592
Visit website 3593
Visit website 3594
Visit website 3595
Visit website 3596
Visit website 3597
Visit website 3598
Visit website 3599
Visit website 3600
Visit website 3601
Visit website 3602
Visit website 3603
Visit website 3604
Visit website 3605
Visit website 3606
Visit website 3607
Visit website 3608
Visit website 3609
Visit website 3610
Visit website 3611
Visit website 3612
Visit website 3613
Visit website 3614
Visit website 3615
Visit website 3616
Visit website 3617
Visit website 3618
Visit website 3619
Visit website 3620
Visit website 3621
Visit website 3622
Visit website 3623
Visit website 3624
Visit website 3625
Visit website 3626
Visit website 3627
Visit website 3628
Visit website 3629
Visit website 3630
Visit website 3631
Visit website 3632
Visit website 3633
Visit website 3634
Visit website 3635
Visit website 3636
Visit website 3637
Visit website 3638
Visit website 3639
Visit website 3640
Visit website 3641
Visit website 3642
Visit website 3643
Visit website 3644
Visit website 3645
Visit website 3646
Visit website 3647
Visit website 3648
Visit website 3649
Visit website 3650
Visit website 3651
Visit website 3652
Visit website 3653
Visit website 3654
Visit website 3655
Visit website 3656
Visit website 3657
Visit website 3658
Visit website 3659
Visit website 3660
Visit website 3661
Visit website 3662
Visit website 3663
Visit website 3664
Visit website 3665
Visit website 3666
Visit website 3667
Visit website 3668
Visit website 3669
Visit website 3670
Visit website 3671
Visit website 3672
Visit website 3673
Visit website 3674
Visit website 3675
Visit website 3676
Visit website 3677
Visit website 3678
Visit website 3679
Visit website 3680
Visit website 3681
Visit website 3682
Visit website 3683
Visit website 3684
Visit website 3685
Visit website 3686
Visit website 3687
Visit website 3688
Visit website 3689
Visit website 3690
Visit website 3691
Visit website 3692
Visit website 3693
Visit website 3694
Visit website 3695
Visit website 3696
Visit website 3697
Visit website 3698
Visit website 3699
Visit website 3700
Visit website 3701
Visit website 3702
Visit website 3703
Visit website 3704
Visit website 3705
Visit website 3706
Visit website 3707
Visit website 3708
Visit website 3709
Visit website 3710
Visit website 3711
Visit website 3712
Visit website 3713
Visit website 3714
Visit website 3715
Visit website 3716
Visit website 3717
Visit website 3718
Visit website 3719
Visit website 3720
Visit website 3721
Visit website 3722
Visit website 3723
Visit website 3724
Visit website 3725
Visit website 3726
Visit website 3727
Visit website 3728
Visit website 3729
Visit website 3730
Visit website 3731
Visit website 3732
Visit website 3733
Visit website 3734
Visit website 3735
Visit website 3736
Visit website 3737
Visit website 3738
Visit website 3739
Visit website 3740
Visit website 3741
Visit website 3742
Visit website 3743
Visit website 3744
Visit website 3745
Visit website 3746
Visit website 3747
Visit website 3748
Visit website 3749
Visit website 3750
Visit website 3751
Visit website 3752
Visit website 3753
Visit website 3754
Visit website 3755
Visit website 3756
Visit website 3757
Visit website 3758
Visit website 3759
Visit website 3760
Visit website 3761
Visit website 3762
Visit website 3763
Visit website 3764
Visit website 3765
Visit website 3766
Visit website 3767
Visit website 3768
Visit website 3769
Visit website 3770
Visit website 3771
Visit website 3772
Visit website 3773
Visit website 3774
Visit website 3775
Visit website 3776
Visit website 3777
Visit website 3778
Visit website 3779
Visit website 3780
Visit website 3781
Visit website 3782
Visit website 3783
Visit website 3784
Visit website 3785
Visit website 3786
Visit website 3787
Visit website 3788
Visit website 3789
Visit website 3790
Visit website 3791
Visit website 3792
Visit website 3793
Visit website 3794
Visit website 3795
Visit website 3796
Visit website 3797
Visit website 3798
Visit website 3799
Visit website 3800
Visit website 3801
Visit website 3802
Visit website 3803
Visit website 3804
Visit website 3805
Visit website 3806
Visit website 3807
Visit website 3808
Visit website 3809
Visit website 3810
Visit website 3811
Visit website 3812
Visit website 3813
Visit website 3814
Visit website 3815
Visit website 3816
Visit website 3817
Visit website 3818
Visit website 3819
Visit website 3820
Visit website 3821
Visit website 3822
Visit website 3823
Visit website 3824
Visit website 3825
Visit website 3826
Visit website 3827
Visit website 3828
Visit website 3829
Visit website 3830
Visit website 3831
Visit website 3832
Visit website 3833
Visit website 3834
Visit website 3835
Visit website 3836
Visit website 3837
Visit website 3838
Visit website 3839
Visit website 3840
Visit website 3841
Visit website 3842
Visit website 3843
Visit website 3844
Visit website 3845
Visit website 3846
Visit website 3847
Visit website 3848
Visit website 3849
Visit website 3850
Visit website 3851
Visit website 3852
Visit website 3853
Visit website 3854
Visit website 3855
Visit website 3856
Visit website 3857
Visit website 3858
Visit website 3859
Visit website 3860
Visit website 3861
Visit website 3862
Visit website 3863
Visit website 3864
Visit website 3865
Visit website 3866
Visit website 3867
Visit website 3868
Visit website 3869
Visit website 3870
Visit website 3871
Visit website 3872
Visit website 3873
Visit website 3874
Visit website 3875
Visit website 3876
Visit website 3877
Visit website 3878
Visit website 3879
Visit website 3880
Visit website 3881
Visit website 3882
Visit website 3883
Visit website 3884
Visit website 3885
Visit website 3886
Visit website 3887
Visit website 3888
Visit website 3889
Visit website 3890
Visit website 3891
Visit website 3892
Visit website 3893
Visit website 3894
Visit website 3895
Visit website 3896
Visit website 3897
Visit website 3898
Visit website 3899
Visit website 3900
Visit website 3901
Visit website 3902
Visit website 3903
Visit website 3904
Visit website 3905
Visit website 3906
Visit website 3907
Visit website 3908
Visit website 3909
Visit website 3910
Visit website 3911
Visit website 3912
Visit website 3913
Visit website 3914
Visit website 3915
Visit website 3916
Visit website 3917
Visit website 3918
Visit website 3919
Visit website 3920
Visit website 3921
Visit website 3922
Visit website 3923
Visit website 3924
Visit website 3925
Visit website 3926
Visit website 3927
Visit website 3928
Visit website 3929
Visit website 3930
Visit website 3931
Visit website 3932
Visit website 3933
Visit website 3934
Visit website 3935
Visit website 3936
Visit website 3937
Visit website 3938
Visit website 3939
Visit website 3940
Visit website 3941
Visit website 3942
Visit website 3943
Visit website 3944
Visit website 3945
Visit website 3946
Visit website 3947
Visit website 3948
Visit website 3949
Visit website 3950
Visit website 3951
Visit website 3952
Visit website 3953
Visit website 3954
Visit website 3955
Visit website 3956
Visit website 3957
Visit website 3958
Visit website 3959
Visit website 3960
Visit website 3961
Visit website 3962
Visit website 3963
Visit website 3964
Visit website 3965
Visit website 3966
Visit website 3967
Visit website 3968
Visit website 3969
Visit website 3970
Visit website 3971
Visit website 3972
Visit website 3973
Visit website 3974
Visit website 3975
Visit website 3976
Visit website 3977
Visit website 3978
Visit website 3979
Visit website 3980
Visit website 3981
Visit website 3982
Visit website 3983
Visit website 3984
Visit website 3985
Visit website 3986
Visit website 3987
Visit website 3988
Visit website 3989
Visit website 3990
Visit website 3991
Visit website 3992
Visit website 3993
Visit website 3994
Visit website 3995
Visit website 3996
Visit website 3997
Visit website 3998
Visit website 3999
Visit website 4000
